I have an SSIS package that gets data from a SQL SERVER 2000 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
I have 2 OLEDB destinations, 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 table B.
When I run the 2nd data flow task that imports data from table B to table C, 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, but with column 1 and column 2 of type varchar.
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 as I did in the query and redirect rows only when conversion error occurs?
I'd want all the rows whose column 1 and column 2 cannot be converted or truncated to go to the error table. some rows for the above 2 columns have values such as 001-000001-01, ABCDEFG, $1234567890,'2013-09-11 19:28:28.670'
Instead of using the data flow task, I tried to use an execute sql task and tried to
do the following..(which sounded like a better approach to me)
insert into table err
select column A,
where isnumeric(column1) and isnumeric(column2)=0
but isnumeric($1234567890) and such values is returning a value 1, due to the limitation of isnumeric.
Is there any other way of handling it in a sql script without using isnumeric or by using isnumeric and any other function in a sql script..so that all non-numeric values can be inserted into error table?
Could someone please suggest the best way of handling this and advice which approach would be the best, to resolve this issue.
is there anyway of using a derived column transformation when redirecting row to error output. I mean oledb source -> red arrow->derived column-> red arrow-> error table?
It is a bit urgent.
Thanks a million in advance! Any logic/code would be greatly appreciated.