DTS Transformation Error handling
-
Hi All,
Could you please let me know how to handle the record level error handling
during the process of source to destination transformation.I know for sure
that an active script transformation option is choosed over here.
The errors that needs to be handled are datatype mismatch,overflow of the value etc.
Regards
Suresh
-
This was one problem that has been (and still is) bugging me
. Seems that DTS does not give any proper error handling capabilities, except for writing to logs.You could try using On Error Resume Next in your ActiveX script, but that's about it.
Error handling like On Error Goto <Line Number> will only work if you are creating your own program using the DTS object heirarchy.
-
Hi Arya,
Thanks for the reply,but unfortunately the problem with on error resume next is it ignores all the errors and will proceed further.
suresh
-
You are trying to do too much in the transformation task. I also guess you want to know which records failed to import.
The answer is as follows:
Create a staging table that has columns all set to varchar(4000) or (8000). Import all your records into here with a straight bulk insert.
Then write a stored procedure to transfer the data from the staging table into your live table. This sp should make certain checks to ensure the data matches the datatype, length expected etc.
If it does not, write the record to an error table.
This gives you:
a) the live table with correct data
b) the records that error
c) a way to complete the job without it failing halfway through.
A little more work, but worth it. I would only recommend using an activex task on trusted data, with very few transformations required. It is also very slow.
------------------------------
The Users are always right - when I'm not wrong!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply