Migrating DTS Active X ADO script to SSIS

Question

Migrating DTS Active X ADO script to SSIS

Jeffrey Pasqua

SSC Enthusiast

Points: 122
More actions
August 1, 2006 at 12:03 pm

#114826

Hi All,
In SQL Server 2000 DTS, I had an Active X script that, using ADO, performed the following basic operation:
Retrieve a list of account numbers through Recordset1
While Not Recordset1.eof
Build a sql statement based on fields from current row of Recordset1
Open Recordset2 based on the sql statement built
Run an update query based on results from recordset 2
Loop
I want to bring this over to SSIS, but i'd like to take advantage of the most current "ways of doing things" instead of just using SSIS's activex script.
Does anyone have any recommendations on the best way to handle this type of thing in SSIS? A script task? A script component? I've never really worked w/ ADO.net so if it involves that I'd have to pick up some pointers... any suggestions?
Thanks in advance
Jeff
August 4, 2006 at 8:00 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Peter Schott SSCrazy Eights Points: 9675 More actions · Answer 1

From your description, it sounds like you're talking about a "For Each" container. Check out http://www.sqlis.com for Shredding a Record set - http://www.sqlis.com/default.aspx?59

You may also look at their For Each File entry. The examples are a little old, but I think they are still valid.

-Pete

Peter Schott SSCrazy Eights Points: 9675 More actions · Answer 2

You'll also want to look at Expressions to build your SQL queries into variables. Not as much fun, but it's doable. I'd recommend getting your text editor out to do it, though. The expression editor is a little tough to work with to build a SQL Statement and you'll want to preview it based on some pre-set variables.

Watch out for the "Delay Validation" settings - you'll probably want them all to be true in this case so your package won't use the values stored in your variables when it starts.

You'll also want to preview your expression results and paste them into SSMS to test or at least check syntax. I spent several hours getting this right for some dynamic queries I'd built. Good thing is that the package now pretty much just works and has saved me time since.

-Pete

Regan Galbraith SSCertifiable Points: 5227 More actions · Answer 3

BTW - does this need to be done in scripts at all? this sounds like something that could be done in SQL.... granted, dynamic sql, but still something done inside an SP.

Peter Schott SSCrazy Eights Points: 9675 More actions · Answer 4

Good catch. I didn't get beyond the recordset and activex when I posted my answer. If this is all that the whole DTS is doing, then a SQL Cursor could work on this, but it could also probably work with sets with some re-design. The dynamic sql is always fun, but quite doable and probably easier to work with than my suggestion.

-Pete