Convert String to Integer with SSIS from .CSV

1
votes

I am using a .CSV file and I am using SSIS 2008.

Source: .CSV

Destination: Database

SSIS see all the columns in my .CSV as a string.

But I need to push 1 column into a database column of type INT.

Nothing want to work though, I have placed a DataConversion in my SSIS package between the source and the destination and none of the Data Type's of integer wants to work.

Error message:

The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data."

Any input on whats going on here? It does not even want to work using the Wizard.

Edit: Sample data as requested------------------------------------------------------------------------

Name|ID

Mike|1266

John|NULL

1
type-conversionssis
can you perhaps paste a row or two of the CSV file?? I would like to see the data that you are passing in. Are you 100% there are no characters in that column? – SQLGuru
It's got something to do with me passing a NULL value into the database. But I am allowing NULL's in my database column. – Etienne
any chance of seeing the data?? just saaw that you r from CT.... likewise... :) – SQLGuru
Please see above, added it. – Etienne
how have you defined the datatype of the column in the CSV stage? – SQLGuru

1 Answers

4
votes

ok... your issue is the fact that you have the physical value "NULL" in your CSV. these should be left blank. By having "NULL" there, it is trying to convert the string "NULL" to an int which it cannot do.

Just make sure all "NULL" values are blank (zero length string). that should sort you out.