Hmmm... from what I just read in BOL...

To save an entire result set of a SELECT statement into a global variable using the Execute SQL task

From the Connection toolbar,...

Re:

Exec SQL task to capture the id you need and select it as an output variable...

This output variable is an array? Cos my first transformation will return 12,000 records?

You...

Oh, and you need 12000 id's?  I thought you needed a single value.  Well yes, you can store lists/tables/arrays as globals, but you may want to consider another approach.  If...

You bet!

After the first transformation, use an Exec SQL task to capture the id you need and select it as an output variable. Create copies of your two connections and do the...

Ahhh... just re-read the question.  Yes, Steve is absolutely right, if it's as simple as only a set of transformations between two connections, you put one "candy-cane" (workflow) between them...

Follow up with an ActiveX script and set the first task to waiting if what you want is to loop.  You could also dynamically change the Source SQL string before...

This can all easily be done in DTS.  See

http://www.sqlservercentral.com/columnists/jsack/capturingtheerrordescriptioninastoredprocedure.asp

and

http://www.sqldts.com/Default.aspx

for ideas on how to write your DTS scripts.  You will need to write some DDL statements in an Execute...

try the dateadd function

it exists in both TSQL and VB but the parameters are different

DTS treats Excel like a table.  Just dump your data into a data page and let your formulas pull from there.

http://www.sqlservercentral.com/columnists/jsack/capturingtheerrordescriptioninastoredprocedure.asp

Create a template Workbook where DTS can pick it...

Package

Save As

Visual Basic File

But why don't you want to use the Dynamic Properties Task?  It's easy and extremely flexible. 

Did you know you can also pass Global Vars in and...

If you are going to do everything in an ExecSQL task, you create one connection icon.  It stands alone.  You create one ExecSQL task.  it stands alone also.  Do not...

Well, it gets so easy in 2000 I have almost forgotten the pain of 7.  Pester your boss to upgrade.

In the meantime, here are your options:

1 - Do what you...

Create an icon for it that stands alone and has no value to your package.  Select that  connection.  Delete the icon.  Boom - gone.  You will now see in your...

You "set" objects.  You simply assign values to variables. strSQLText is not an object.  It is a variable.

Also, you are trying to use the DTS connection object to open an ADO...

BOL is a searchable reference - it doesn't read like a book.

Wrox SQL Server 2000 Programming is the best place to start.  Yes, it's a big book.  You might only...