Results 1 to 4 of 4

Thread: DTS Transform

  1. #1
    Join Date
    Oct 2003
    Posts
    311

    Unanswered: DTS Transform

    I get this error:

    column 6 ('CheckDate', dbType_dbtimestamp), status 6: dat over flow
    invalid character value for cast specification

    Here is the sql generated :

    CREATE TABLE [DMS].[dbo].[Master] (
    [Date_Entered] smalldatetime NULL,
    [Initials] nvarchar (10) NULL,
    [VendorName] nvarchar (50) NULL,
    [CheckNum] nvarchar (20) NULL,
    [ExpenseType] nvarchar (10) NULL,
    [CheckDate] smalldatetime NULL,
    [CheckAmount] money NULL
    )


    IM guessing i need to change date_enteed type or do a cast

    any help pls

    !~<M

  2. #2
    Join Date
    Oct 2003
    Posts
    311
    is there anybody who has any idea about this?

  3. #3
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Is this the table DTS is inserting into, or is it actually trying to create the table? If it's trying to insert, change the CheckDate to datetime instead of smalldatetime and see if that fixes it.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    smalldatetime to datetime will not make a difference if value in the source is not a date. I'd suggest to change CheckDate and possibly Dtae_Entered to varchar(25), but add 2 calculated fields that would convert a valid date to date or NULL if it's not. Put them at the end of the table and do not insert anything into them (it'll fail if you try).

    CREATE TABLE [DMS].[dbo].[Master] (
    [Date_Entered] varchar(25) NULL,
    [Initials] nvarchar (10) NULL,
    [VendorName] nvarchar (50) NULL,
    [CheckNum] nvarchar (20) NULL,
    [ExpenseType] nvarchar (10) NULL,
    [CheckDate] varchar(25) NULL,
    [CheckAmount] money NULL,
    [Date_Entered_Calc] as case when isdate([Date_Entered])=1 then cast([Date_Entered] as datetime) else cast(null as datetime) end,
    [CheckDate_Calc] as case when isdate([CheckDate])=1 then cast([CheckDate] as datetime) else cast(null as datetime) end
    )
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •