Converting SQL 7.0 DTS to SSIS

Question

Converting SQL 7.0 DTS to SSIS

paul.corujo

SSCommitted

Points: 1891
More actions
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

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

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

SSIS\SSRS\SSAS SSCertifiable Points: 6038 More actions · Answer 1

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

paul.corujo SSCommitted Points: 1891 More actions · Answer 2

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?

VG-619426 Hall of Fame Points: 3914 More actions · Answer 3

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

-VG

SSIS\SSRS\SSAS SSCertifiable Points: 6038 More actions · Answer 4

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,

paul.corujo SSCommitted Points: 1891 More actions · Answer 5

paul.corujo

SSCommitted

Points: 1891

August 26, 2008 at 2:02 pm

#863375

Awesome, thanks all. I got it working.

SSIS Guy SSCommitted Points: 1667 More actions · Answer 6

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/

Phil Parkin SSC Guru Points: 245813 More actions · Answer 7

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?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.