| Author |
Topic |
|
LOOKUP_BI
Constraint Violating Yak Guru
295 Posts |
 Posted - 2008-02-11 : 16:54:12
|
| Hi Guys,1) I have data coming in from flat file everyday.On failure I would require Error Table to be updated with problematic rows.Otherwise rows would be updated to a Staging Table.Problem is I have been asked to also return the row number for the problematic rows, so that it's easier for the Client to resend failed rows.How do I do this ?2) I have realize that even if row coming in has some invalid data type in more than 1 column.ErrorColumn, in Error table just return 1 column value.Is this how it works? OR is there a way for it to return the Error_Column for the other Column's as well.I have been struggling with this since last week.Do help me out. |
|
|
Qualis
Posting Yak Master
145 Posts |
Posted - 2008-02-11 : 23:14:53
|
| Create a script transform before your destination that adds a column named RowNumber. Inside the script class, create a private variable and name it something like _rowNumber as an int data type. Inside the function that is called when a row comes in, increment the variable by 1 and pass it to the column you created. |
 |
|
|
LOOKUP_BI
Constraint Violating Yak Guru
295 Posts |
Posted - 2008-02-12 : 08:38:33
|
| Hi I did create a RowNumber as a new column in my Table, I am pretty weak in the scripting part.Could you please show me how to do this.Thank You |
|
|
|
Qualis
Posting Yak Master
145 Posts |
Posted - 2008-02-13 : 10:09:12
|
| This is a good segway to a blog post on the topic. I'll whip one up for you. Give me 30 mins or so. ;) |
|
|
|
LOOKUP_BI
Constraint Violating Yak Guru
295 Posts |
Posted - 2008-02-13 : 11:06:56
|
Thank You.... |
|
|
|
Qualis
Posting Yak Master
145 Posts |
Posted - 2008-02-13 : 11:32:55
|
| Here you go. Sorry it took a little longer. I got distracted! ;)http://weblogs.sqlteam.com/jamesn/archive/2008/02/13/60509.aspx |
|
|
|
LOOKUP_BI
Constraint Violating Yak Guru
295 Posts |
Posted - 2008-02-13 : 11:38:15
|
| Thank You..I will give it a try and respond back If I need any help. |
|
|
|
LOOKUP_BI
Constraint Violating Yak Guru
295 Posts |
Posted - 2008-02-13 : 15:08:13
|
Hi There...hmmm I think I'm doing a mistake somewhere. I have added RowNumber in InputOutputColumn in Script Component and add the following line of code in ScriptPublic Class ScriptMain Inherits UserComponent Private _RowNumber As Integer = 1 Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) Row.ErrorDescription = ComponentMetaData.GetErrorDescription(Row.FlatFileSourceErrorCode) Row.RowNumber = _RowNumber _RowNumber += 1 End SubEnd ClassThe code in Bold, Im using it to get Error Description.Problems is, Instead of returning only the Row Number's for rows with ERROR.Value of RowNumber is returned as 1, 2, 3 , 4....and so on.[INCREMENTAL]......Where am I going wrong |
|
|
|
Qualis
Posting Yak Master
145 Posts |
Posted - 2008-02-13 : 16:09:40
|
| Did you attach this to the Error Output on the Flat File Source? If so, the script will only receive the rows that error. You cannot get the row number using that method. I'm looking for a way to do that, but I can't think of a method off the top of my head. To use my method, set all the fields to string and a size large enough to hold them. Then do whatever checks you need to determine the records as invalid. |
|
|
|
|
Error Table in SSIS