Code Sample for SSIS Script Component

Greetings,

I have been developing VBA apps in Access and Excel for sometime and am fairly proficient in VBA. Now we are moving all of our data to SQL Server 2005. I am in need of learning how to write code for the Script Component of a data flow task. And so I have a couple of questions.

First, are there any books you recommend for learning ? (I'm not even sure what I need to learn: .NET? ADO.NET?)

And as a follow-up, any good websites that provide good reference documentation?

And my second question is more specific to my current problem. If I had a bit of code to get me started, I'm sure I could scream all the way to the bottom of the hill.

Data source is coming from a sort task where the data is sorted by STATUS and then MOD_DATE and the AUDIT_ID. I need to read each row and compare it to the next row. If STATUS is the same, discard the second row. When STATUS is different, send the first row to the output (to be used by the next task in the data flow). Using the "different" row from step 3, go to step 2.

I know how to write if statements, case statements, for/next statements. I'm just not understanding how to read the rows in and then send them back out. I've been searching for some sample code but everything I find tends to be solving much bigger issues.

Any help you can provide would be much appreciated.

Rob

1.
Which kind of book do you need ? T-SQL or SQL-CLR integration ?
For T-SQL I suggest you to buy a good basic book from amazon (that ha a good rating) just to learn the basics and then the buy the following two monuments by Itzik Ben-gan (MS Press)
INSIDE SQL SERVER 2005: T-SQL QUERYING
INSIDE SQL SERVER 2005: T-SQL PROGRAMMING
They are really hard... I don't know anything better.
For the second I suggest PRO SQL SERVER 2005 ASSEMBLY by Dewson (Apress)

2.
http://www.sql-server-performance.com
http://www.sqlservercentral.com
Google...

3.
can you explain better ? are you trying to code it in T-SQL or are you trying with a CLR stored procedure ? In the second case to send back a row you may use Pipe.Send or Pipe.ExecuteAndSend methods.

__

? www.carlop.com × carlop-dev.blogspot.com

|||

carlop wrote:

3.
can you explain better ? are you trying to code it in T-SQL or are you trying with a CLR stored procedure ? In the second case to send back a row you may use Pipe.Send or Pipe.ExecuteAndSend methods.

carlop - thank you for the book suggestions. I'll check them out.

Here is a sample of data. I am after capturing each row when the STATUS changes (highlighted in yellow). A total of six rows: one each of Not Ready, Hold and Complete and three of Active. All of the other rows will be discarded.

I'm thinking this is best done with a CLR procedure, however, I'm open to suggestions.

Rob

AUDIT_ID OBJECTID MODIFIED_DATE STATUS 2753029 124248079 4/26/2007 11:30 Active 2753035 124248079 4/26/2007 11:30 Active 2753076 124248079 4/26/2007 11:33 Active 2754823 124248079 4/26/2007 14:31 Not Ready 2794102 124248079 4/27/2007 7:29 Not Ready 2817836 124248079 4/27/2007 17:06 Active 2817859 124248079 4/27/2007 17:09 Active 2818286 124248079 4/27/2007 18:00 Hold 2818473 124248079 4/27/2007 18:38 Active 2818483 124248079 4/27/2007 18:44 Active 2989403 124248079 5/1/2007 7:14 Active 3022521 124248079 5/1/2007 7:19 Active 3042121 124248079 5/1/2007 10:45 Complete 3082880 124248079 5/2/2007 7:07 Complete