August 5, 2010 at 1:32 pm
I inherited a package that generally runs well and gets the job done. However, occasionally when doing a date conversion from SAP date to SQL datetime, it encounters a bad date. At that point, it fails the job, terminates and writes to the error log the field that failed.
What I would love is 1) do not fail the job, simply write to the error log and move to the next record; or 2) include the record ID in the error message so it can be corrected.
Any ideas????!!! Thanks!
August 5, 2010 at 2:58 pm
Janie.Carlisle (8/5/2010)
I inherited a package that generally runs well and gets the job done. However, occasionally when doing a date conversion from SAP date to SQL datetime, it encounters a bad date. At that point, it fails the job, terminates and writes to the error log the field that failed.What I would love is 1) do not fail the job, simply write to the error log and move to the next record; or 2) include the record ID in the error message so it can be corrected.
Any ideas????!!! Thanks!
You can redirect rows in "Error Output" in your source under data flow component so that to capture error records to a table or so.
[font="Arial"]BASKAR BV
http://geekswithblogs.net/baskibv/Default.aspx
In life, as in football, you won’t go far unless you know where the goalposts are.[/font]
August 5, 2010 at 9:57 pm
Use TRY...CATCH to catch exceptions. Here is the documentation.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
August 6, 2010 at 4:23 am
Adiga (8/5/2010)
Use TRY...CATCH to catch exceptions. Here is the documentation.
Not everything in SSIS is done with T-SQL. A better option - for standard components - is to use the error output, as mentioned above, or to do some filtering upfront with a derived column.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 6, 2010 at 6:31 am
I am not sure I understand why that will keep the package from failing. Sounds like it will write the bad record to a table but then fail the package anyway. Am I wrong? BTW, it is using a derived column for the date conversion which is where the failure occurs. Thanks!
August 6, 2010 at 6:35 am
Janie.Carlisle (8/6/2010)
I am not sure I understand why that will keep the package from failing. Sounds like it will write the bad record to a table but then fail the package anyway. Am I wrong? BTW, it is using a derived column for the date conversion which is where the failure occurs. Thanks!
There are several properties that you can set to prevent your package from failing, such as FailPackageOnFailure. You can also set the number of errors that can occur before the package fails.
But, the best option is to sanitize your input before you try to do the conversion. This can be done in a derived column:
(column == bad code) ? "Error" : "Converted Value"
or with a conditional split. In the conditional split you filter out the unwanted rows and you can easily write them to a seperate table.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 6, 2010 at 8:03 am
Sounds like an interesting option although I am not sure how to do it. What is happening now is the package is going into SAP and extracting data from a table it brings in all the changes to a table as well as any new records - does a count between the two and union them. Then it pushes those unioned records to a derived column task where it converts the SAP date string to a SQL datetime field. There are about 12 fields that it converts. This is where one of the fields occasionally fails causing the package to fail. I was looking at configuring it so it pushes the bad record to a table but need some guidance. After I configure error options so it redirects the row, do I need to add a recordset destination task? If so, how do I configure that? I am guessing that it needs to be across from the Derived Column task such that if it sees a bad record it pushes it to the recordset destination task and if it sees good records they get pushed down to the next task in the sequence which is a staging table. Can send the schematic if you'd like or at least I think I can. Thanks for help.
August 6, 2010 at 8:58 am
Janie.Carlisle (8/6/2010)
Sounds like an interesting option although I am not sure how to do it. What is happening now is the package is going into SAP and extracting data from a table it brings in all the changes to a table as well as any new records - does a count between the two and union them. Then it pushes those unioned records to a derived column task where it converts the SAP date string to a SQL datetime field. There are about 12 fields that it converts. This is where one of the fields occasionally fails causing the package to fail. I was looking at configuring it so it pushes the bad record to a table but need some guidance. After I configure error options so it redirects the row, do I need to add a recordset destination task? If so, how do I configure that? I am guessing that it needs to be across from the Derived Column task such that if it sees a bad record it pushes it to the recordset destination task and if it sees good records they get pushed down to the next task in the sequence which is a staging table. Can send the schematic if you'd like or at least I think I can. Thanks for help.
Yes. You need to connect the red arrow to the data flow destination. Good records would be pushed down through the green arrow to the further components.
[font="Arial"]BASKAR BV
http://geekswithblogs.net/baskibv/Default.aspx
In life, as in football, you won’t go far unless you know where the goalposts are.[/font]
August 6, 2010 at 9:10 am
I would redirect errors to a log or table then move on to the next record. I would also tack on a send email so that I am notified of errors.
August 6, 2010 at 10:01 am
ROAR!!! TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at VTTK to Staging [DTS.Pipeline]: input column "TKNUM" (25410) has lineage ID 24473 that was not previously used in the Data Flow task.
Error at VTTK to Staging [DTS.Pipeline]: "component "Error Output to Table" (25400)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".
Error at VTTK to Staging [DTS.Pipeline]: One or more component failed validation.
Error at VTTK to Staging: There were errors during task validation.
(Microsoft.DataTransformationServices.VsIntegration)
------------------------------
BUTTONS:
OK
------------------------------
August 9, 2010 at 12:35 am
The error indicates that there are some issues with the metadata in your package. Try to refresh it (by opening and closing the component that gives the error).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 9, 2010 at 2:33 pm
Yeah!!!! It took a little trying and retrying to get rid of the validation errors but now it is working like a charm. In fact, I think I will change all the other packages to work the same way. It is a lot more efficient and considerably more informative allowing me to get the errors corrected right away. Thanks everyone for assistance.
Janie
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply