February 9, 2011 at 10:01 pm
Ok experts I have an issue which has me totally baffled. I have been searching the web, BOL and I haven't found anything for something which I am thinking should be straight forward.
I am trying to configure the Error Output of an OLE DB Source component. So I have the prior mentioned component connected to a Derived column component (just using this to change my status column to indicate error in row), row count component which eventually connects to the OLE DB Destination component which I am expecting to capture any errors.
I have populated a datetime field in my source to have the following characters 'ffff' (clearly not a date). My conversion is done in the OLE DB Source Component using T-SQL Cast function. When executed the error fatally stops the execution at the OLE DB Source component and never is redirected.
Now I have followed the BOL description of how to configure an error output to the letter.
I also tried to remove the Derived column and row count components and connect the OLE DB Destination component directly to the failing path of the OLE DB Source component. Still no luck.
The last thing I tried was to replace the OLE DB Destination component with a excel file destination component (got a hole new set of errors here but basically the connection object wasn't supported in the 64bit version of SSIS),
Please help.
February 10, 2011 at 3:14 am
You should mention it is a SSIS related question, so people can place it in the right context 🙂
My guess is that since the CAST function fails, the whole statement fails, ergo, the source component fails and no rows are returned.
Error output on the source is usually used for truncation errors. Try putting a large string (50 chars) into a 20 char string, that should probably work.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 10, 2011 at 4:14 am
Thanks Koen for your response and yes my apologize this is a SSIS related question.
I tried your suggestion of pushing a larger string in a smaller string field i.e. Abbbb into nvarchar(1). Unfortunately this didn't even error out it loaded the value as normal.
Now I am wondering based on your answer if I am using the error output in the correct way. My aim here is simply to redirect rows which have incorrect or incompatible data types or some other error such as truncation to an error table for later handling. If how I have implemented this as described earlier is incorrect please let me know the best practice for doing this and proper implementation.
Thanks,
February 10, 2011 at 5:19 am
Mark F-428640 (2/10/2011)
Thanks Koen for your response and yes my apologize this is a SSIS related question.I tried your suggestion of pushing a larger string in a smaller string field i.e. Abbbb into nvarchar(1). Unfortunately this didn't even error out it loaded the value as normal.
Now I am wondering based on your answer if I am using the error output in the correct way. My aim here is simply to redirect rows which have incorrect or incompatible data types or some other error such as truncation to an error table for later handling. If how I have implemented this as described earlier is incorrect please let me know the best practice for doing this and proper implementation.
Thanks,
You should check the error configuration of the source component. If everything is set to Ignore Failure, of course nothing will happen.
Make sure that the appropriate items are set to Redirect Row.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 10, 2011 at 6:05 am
Yes everything is set to redirect row in the source component.
February 10, 2011 at 6:11 am
Mark F-428640 (2/10/2011)
Yes everything is set to redirect row in the source component.
Where did you configure that the input could only be nvarchar(1) long?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 10, 2011 at 6:18 am
In the database table
February 10, 2011 at 6:19 am
Sorry replied to quickly I cast the object in the ole db source t-sql script and also the DB table is set to nvarchar(1)
February 10, 2011 at 7:19 am
Mark F-428640 (2/10/2011)
Sorry replied to quickly I cast the object in the ole db source t-sql script and also the DB table is set to nvarchar(1)
Don't cast it. Just pull the data out of the source as it is.
Then, in the advanced editor of the source, set the input column to a string length of 1. That should do the trick.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 10, 2011 at 7:44 am
hey Koen thanks for your response just to be clear when you say set the input column to a string length of 1. Are you referring to the tab input and output properties -> OLE DB Source Output -> Output Columns?
Sorry I don't use this editor very much
February 10, 2011 at 7:50 am
Mark F-428640 (2/10/2011)
hey Koen thanks for your response just to be clear when you say set the input column to a string length of 1. Are you referring to the tab input and output properties -> OLE DB Source Output -> Output Columns?
Jup, those are the ones.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 10, 2011 at 8:07 am
ok when I attempt this the system throws an error before execution in the design editor (big red X) saying basically myfield cannot convert between unicode and nonunicode data types.
My guess and the reason for my cast operation to begin with is the meta data recognizes the source table as varchar and the destination table as nvarchar therefore it wants me to cast/convert the data type. Set me straight here I am getting confused?
February 10, 2011 at 10:34 am
You can import it as varchar to test the truncation errors, and convert it to nvarchar in the pipeline using a data conversion component.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply