How to catch errors while importing data from one table to another using ssis

Hi!

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,
COLUMN b
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.
amuktaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
DO you need to cast column2?

mlmcc
0
DcpKingCommented:
It's long, but it's step-by-step instructions so be patient - it's really not long!

I did this as a test for your data:
1. create three tables - dbfrom, dbto, and dbbad. dbfrom holds an int (key) and a character field charval varchar(128). The content of charval is sometimes your failed examples and sometimes good numbers, like 512, 1024, etc.
2. dbto has two int fields - id and intval. id will be from the id field in dbfrom; intval is the integer of charval in dbfrom.
3. dbbad is the same as bdfrom, and is used to keep the unacceptable (i.e. unconvertable) records.
4. Start an SSIS project, add a dataflow task to the control flow. At the end we'll want to see what you see in screengrab #1 attached, so make the various task containers now, along with a connection, and fill dbfrom with appropriate data (good and bad).
5. Once you've got your source linked up, connect the Derived Column and edit it. Screengrab #2 shows what to set it up to. You'll be creating a new column, (I called it "IsThisNumeric") as a new column. The expression is important: (DT_I4)charval == (DT_I4)charval ? 1 : 0. The trick here is the non-numeric values will lead to nulls from the conversion, so nulls as a result in the new field.
6. Next, still in the Derived Column, click on the Configure Error Output button at the lower left. When you get there make sure that the column selected is your new column (I called it "IsThisNumeric", remember?). Set the Error and Truncation actions both to Redirect Row. That'll make sure that the rows you don't want go out the Fail link.
7. Connect an OLEDB Data Destination to the fail of the Derived Column and connect it to dbbad.
8. Connect the succeed link of the Derived Column to the Data Conversion and set it up as in screengrab #4. That is, use charval as input, a new name, intval, as the output alias, make intval a 4-byte int (DT_I4). We shouldn't need error checking here, as all the non-numerics should already have been diverted!
9 Connect an OLEDB Data Destination to the output of the Data Conversion and connect that to dbto.
10. Fill dbfrom with good and bad values and try it out!

Good luck

hth

Mike
1-DataFlow.PNG
2-DerCol.PNG
3-DerCol.PNG
4-DataConv.PNG
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.