October 14, 2008 at 2:00 pm
I have Flat File Source
and then I have Data Conversion component in my Data Flow:
COL008 --> Conv8 [DT_DBDATE]
COL009 --> Conv9 [DT_DBDATE]
I want to redirect the rows that failed conversion
to either a text file or SQL table.
How do I do that in Data Flow?
October 14, 2008 at 2:22 pm
In the Data Conversion Task go into "Configure Error Output" and set the desired error types to "Redirect Row". Then you will have a RED (failure) arrow in addition to the GREEN (success) arrow. You can use the Error Output just like you can any other output to log the changes.
Check out this article and the discussion of it as there is a link to a custom error component in it as well:
http://www.sqlservercentral.com/articles/Integration+Services/62662/
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 16, 2008 at 7:15 am
Thank you Jeck.
I'd like to learn more about this
"Configure Error Output" screen.
It has the following columns:
Input or Output
Column
Error (editable)
Truncation (editable)
Description
First of all I don't understand what Truncation column means.
How do I use it?
If understand it correctly when for "Column" column I select "Redirect Row"
it means this column value will be re-directed further to the next step.
But do I have to select "Redirect row" for Truncation?
What if I just re-direct 1 or 2 columns and the rest of the columns will be set to "Ignore failure" does it mean I'll get 2 columns available in my next step? I also assume if you want any values to be redirected
you can't select "Fail component" for any columns otherwise the step will fail and nothinng will be redirected. Am I right?
October 16, 2008 at 8:07 am
Truncation means that the data will not fit in the space allocated. So if you have it set to string 40 and there is a 50 character string it will fail if you have "Fail Component" selected.
Basically each column is some type of error, the columns vary based on the transformation task, and you select what you want to happen on "Error", "Truncation", etc... If you have multiple columns that you are converting you can specify different responses for each column. So if you are converting LastName and FirstName you may want to fail on an error encountered in the LastName and Ignore Failure on FirstName.
I typically redirect on any error and pass the error output to the Custom Component and then take the results from the Custom Component and pass them to a table.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 16, 2008 at 8:26 am
Thank you Jack.
But do I have to pass Error Output to some Custom Component
if for example my converssion from STR to DT_DATE failed?
I know already that it's a conversion failure. What else do I need to know? I mean it's probably enough information to find the cause of the problem and I can just write to the database some CUST_ID,CUST_NAME,ErrorMessage columns.
But that's just a simple conversion example.
You probably right when you do multiple operations
on all the columns. In this case you don't know what kind of error might occur
so you just de-code error codes into error description using your Script component. I will do this test today. It is definately important for good error handling.
October 16, 2008 at 8:30 am
In reality you can do whatever you want with the error output. I just knew that I wanted more than the error code and that I wanted a generic solution.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 16, 2008 at 8:39 am
Jack,
I just read your article at
http://www.sqlservercentral.com/articles/Integration+Services/62662/
and couldn't find how you pass the output from your Transformation Script to a database.
I don't know maybe it's very easy. I've never done it before.
Do you have a Pipeline available from Transformation Script step?
October 16, 2008 at 8:47 am
The script component (or custom Error Details component if you are using that) has an output that you send to an OLE DB Source just like any other component.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 16, 2008 at 11:45 am
Jack,
I am trying to test your script
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim column As IDTSInputColumn90
Dim rowType As Type = Row.GetType()
Dim columnValue As PropertyInfo
Dim strErrorDetails As String = " <field "
Dim strAttributeName As String
Dim strValue As String
.....
and it gives me an error at line:
Dim rowType As Type = Row.GetType()
It highlights the word Type.
When I move the mouse over it says Type "Type" is not defined.
It also complains at the line:
Catch ex As Exception
Type 'Exception' is not defined.
October 16, 2008 at 11:52 am
Jack,
I also want to understand why in my pipeline I see only the original column names what I get from my Flat File source - COL001,COL002,COL003...
Why those new converted COL001_NEW,COL002_NEW,COL003_NEW
don't show up. Is it because at the time the error occured Derived Columns step failed and those new derived columns were not created?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply