I have an SSIS package that gets data from a SQL SERVER 2008 TABLE (table A)on one server to a SQL SERVER 2008 table(table B) on another server and then convert the data and move it to another table.
I'm using a data flow task to do the above. The datatype of 2 columns, column 1 and column 2 in both the tables is varchar(60) and varchar(50) respectively.
I need to import the data from table B which is the staging table to a final table(table C).The data type of the above 2 columns is of type int in table C. I'm converting the above 2 columns to int data type when I import the data, for which I have a dataflow task, in which the OLEDB source has the following query:
SELECT CAST(COLUMN1 AS INT), COLUMN2 AS INT) FROM TABLE B
Hi
I have 2 SQL tables i.e OLEDB Data sources , one which has the correct rows(that have been successfully converted to int) thru green arrow, and the other that has erroneous rows(that throw an error) thru red arrow.(It is configured to redirect row only on conversion error, it has "failed component" option when a truncation error occurs)the oledb destination tables has the same structure as Source Table.
When I run the data flow task that imports data from Source Table to Destination Table, I get an error "invalid character value for cast specification" or the data conversion error. I do not want it throw an error, but rather redirect the erroneous rows to the error table with the same structure as the destination table, with same structure has source table with error no and column name
Would it be a good idea to use a data conversion task and redirect the errors rows, by configuring an error output on conversion as well as truncation or just on conversion or cast the value and redirect rows only when conversion error occurs?
