Post reply

Converting SQL 7.0 DTS to SSIS

  • August 26, 2008 at 10:10 am

    #123062

    I am converting a package from sql 7.0 to 2k5 SSIS. The original package contained a VB script in the transformation which concatenated two columns from the source flat file as well as ltrim(rtrim(Col)) on each column.

    To perform these tasks in SSIS I am using the Derived Column Task and i keep getting Truncate errors. I have verified in the advanced options that all lengths match that of the destination table. Still I get the following error:

    [OLE DB Destination [1490]] Error: There was an error with input column "Column 3" (1665) on input "OLE DB Destination Input" (1503). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".

    Can somebody please point me in the right direction.

    Thanks

  • August 26, 2008 at 11:35 am

    #863290

    This might be because of several issues. First what is your datatypes?. and Also, I belive there are some null values or values at least looks null, but have some empty strings.

    For rt now you can gohead in advance page and select the option On truncation Ignore, But this might end up loosing some data. make sure there is nothing get truncates. ALso, work with some bulit in functions in the SSIS like null functions and types in the derieved expressions.

    thanks

  • August 26, 2008 at 11:51 am

    #863300

    Thanks so much for your response. I turned the truncate to ignore and the error went away. My next issue is when trying to concatenate a date and a time coming in from the flat file to a datetime column in my table. The date and time are both coming in as DT_STR datatypes. Any suggestions?

  • August 26, 2008 at 12:07 pm

    #863311

    You can try using data conversion transformation in between to the data type you wanted, in your case DT-Date.

    -VG

  • August 26, 2008 at 1:28 pm

    #863359

    VG is right. First combine the two columns as one as string. and then convert that combined column into data type of your choice. You will get an issue if there is any null value or empty string in your combined column.

    thanks,

  • August 26, 2008 at 2:02 pm

    #863375

    Awesome, thanks all. I got it working.

  • January 30, 2010 at 10:34 am

    #1111205

    Check DTS xChange. It will not convert your DTS logic to SSIS but also adds many best practies for SSIS.

    http://www.pragmaticworks.com/Products/Business-Intelligence/DTSxChange/

  • January 30, 2010 at 10:36 am

    #1111207

    SSIS Guy (1/30/2010)

    Check DTS xChange. It will not convert your DTS logic to SSIS but also adds many best practies for SSIS.

    http://www.pragmaticworks.com/Products/Business-Intelligence/DTSxChange/

    Do you work for this company?

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply