Search code examples
sqlsql-serverssisbidsfoxpro

Convert from string to int in SSIS

I'm converting a database from one structure into a new structure. The old database is FoxPro and the new one is SQL server. The problem is that some of the data is saved as char data in foxpro but are actually foreign key tables. This means they need to be int types in sql. Problem is When i try to do a data conversion in SSIS from any of the character related types to an integer, I get something along the following error message:

There was an error with the output column "columnName"(24) on output "OLE DB Source Output" (22). The column status returned was : "The value could not be converted because of potential loss of data".

How do i convert from a string or character to an int without getting the potential loss of data error. I hand checked the values and it looks like all of them are small enough to fit into an int data type.

Solution

  • Data source -> Data Conversion Task.

    In Data Conversion Task, click Configure Error Output

    For Error and Truncation, change it from Fail Component to Redirect Row.

    Now you have two paths. Good data will flow out of the DCT with the proper types. The bad data will go down the Red path. Do something with it. Dump to a file, add a data view and inspect, etc.