DTS: Inserting the contents of a global variable into a table via a loop

Hello,

I am wondering if anyone can help me. I am fairly new to DTS and am having some difficulty getting something to work. I will explain below:

I have one table (table_a) which contains a listing of contact details. I want to copy the data from table_a to table_b. The tricky part is that I want to insert certain items into table_b depending on a conditional statement on the items in table_a. (ie. If value in table_a.column_a is 1 then insert "good" into table_b.column_a, etc)

I have done a lot of looking and found that an ActiveX Script Task is the answer. So I now have an Execute SQL Task that selects all the data from table_a into a global variable recordset, an ActiveX Script Task that loops through the recordset, determines the necessary values and modifies the SQL Statement of a temporary Execute SQL Task with the required INSERT statment (as explained in this article: http://www.databasejournal.com/features/mssql/article.php/1461581).

I run it and it all runs fine except that no data is inserted into the table!!! The SQL Statement in the temp Task does get modified with the INSERT statement and inserts the data fine when forced to run. But it appears that the temp Task is not being executed within the ActiveX Script.

Can anybody tell me how to force a step to run in this loop-based scenario, or if there is a much better way I could do this.

Thanks for any help. LeeIf I understand what you are trying to do...
Inputvalue = 10

if Inputvalue < 10, Output Value = 20, else Output Value = 30.

If you are trying to make those types of changes, then you can do some simple Transforms.

With two data sources, you can add a Transform Data Task.

You can modify (delete, then add) an activex transform within the Transform task that will allow you to modify outgoing data based on the data coming in.

If you need more help on this, see books online, ActiveX Script, then choose DTS.|||Thanks, yes. I was just looking into that as I received your post and it does indeed look like the ActiveX Transform option is the way to go.

I'm impressed you could decipher what I was after!

Thanks for the help.