SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 Error Table in SSIS
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 02/11/2008 :  16:54:12  Show Profile  Reply with Quote
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

USA
145 Posts

Posted - 02/11/2008 :  23:14:53  Show Profile  Reply with Quote
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.
Go to Top of Page

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 02/12/2008 :  08:38:33  Show Profile  Reply with Quote
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
Go to Top of Page

Qualis
Posting Yak Master

USA
145 Posts

Posted - 02/13/2008 :  10:09:12  Show Profile  Reply with Quote
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. ;)
Go to Top of Page

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 02/13/2008 :  11:06:56  Show Profile  Reply with Quote
Thank You....
Go to Top of Page

Qualis
Posting Yak Master

USA
145 Posts

Posted - 02/13/2008 :  11:32:55  Show Profile  Reply with Quote
Here you go. Sorry it took a little longer. I got distracted! ;)

http://weblogs.sqlteam.com/jamesn/archive/2008/02/13/60509.aspx
Go to Top of Page

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 02/13/2008 :  11:38:15  Show Profile  Reply with Quote
Thank You..I will give it a try and respond back If I need any help.
Go to Top of Page

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 02/13/2008 :  15:08:13  Show Profile  Reply with Quote
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 Script

Public 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 Sub
End Class

The 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

Go to Top of Page

Qualis
Posting Yak Master

USA
145 Posts

Posted - 02/13/2008 :  16:09:40  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000