Unanswered: DTS: Inserting the contents of a global variable into a table via a loop
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/featu...le.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.