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.

DTS w/dynamic source & query

I'm looking for a way to transform the contents of n source tables
into a single destination table. This by itself is no problem.

However, the name of the source tables change, so I'll need to base
the transform task on a global variable that I can update via external
code. Not sure how to do that. I'm ok with executing the package 10
times if there's 10 source tables.

The last unknown piece is modifying the query used for the transform.
There are 10 columns in the source table, but there are 12 columns in
the destination table. I must provide the 2 missing columns. They will
simply contain a year and month, ie. 05 2003.

I'm taking a bunch of source tables (for a given month and year) and
rolling them together into one destination table, and carrying over
the month and year. I assume the month and year would also be globals.
But I'm not sure how to incorporate them into the transform task since
it wants strict SQL syntax.

Any help is appreciated, thanks in advance!"znelson" <znelson@.hpis.net> wrote in message
news:f5fff72a.0405060627.796ea9cb@.posting.google.c om...
> I'm looking for a way to transform the contents of n source tables
> into a single destination table. This by itself is no problem.
> However, the name of the source tables change, so I'll need to base
> the transform task on a global variable that I can update via external
> code. Not sure how to do that. I'm ok with executing the package 10
> times if there's 10 source tables.
> The last unknown piece is modifying the query used for the transform.
> There are 10 columns in the source table, but there are 12 columns in
> the destination table. I must provide the 2 missing columns. They will
> simply contain a year and month, ie. 05 2003.
> I'm taking a bunch of source tables (for a given month and year) and
> rolling them together into one destination table, and carrying over
> the month and year. I assume the month and year would also be globals.
> But I'm not sure how to incorporate them into the transform task since
> it wants strict SQL syntax.
> Any help is appreciated, thanks in advance!

One way to pass in a global variable at runtime is as a parameter to
dtsrun.exe - you can use dtsrunui.exe to build a sample command line for
you, then parameterize it using your preferred language:

http://www.sqldts.com/default.aspx?301

This is useful when the number of parameters is fairly limited. An
alternative if you have many global variables is to put the values in a
database table or .INI file, and use a Dynamic Properties task to retrieve
them and assign them to the variables. This allows for more complex logic,
conditional values based on server name, AD site name etc. Finally, you can
assign global variable values directly in an ActiveX task.

As for how to pass in the month/year to an INSERT, these pages may be
useful:

http://www.sqldts.com/?234
http://www.sqldts.com/?205

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<409a6999$1_2@.news.bluewin.ch>...
> "znelson" <znelson@.hpis.net> wrote in message
> news:f5fff72a.0405060627.796ea9cb@.posting.google.c om...
> > I'm looking for a way to transform the contents of n source tables
> > into a single destination table. This by itself is no problem.
> > However, the name of the source tables change, so I'll need to base
> > the transform task on a global variable that I can update via external
> > code. Not sure how to do that. I'm ok with executing the package 10
> > times if there's 10 source tables.
> > The last unknown piece is modifying the query used for the transform.
> > There are 10 columns in the source table, but there are 12 columns in
> > the destination table. I must provide the 2 missing columns. They will
> > simply contain a year and month, ie. 05 2003.
> > I'm taking a bunch of source tables (for a given month and year) and
> > rolling them together into one destination table, and carrying over
> > the month and year. I assume the month and year would also be globals.
> > But I'm not sure how to incorporate them into the transform task since
> > it wants strict SQL syntax.
> > Any help is appreciated, thanks in advance!
> One way to pass in a global variable at runtime is as a parameter to
> dtsrun.exe - you can use dtsrunui.exe to build a sample command line for
> you, then parameterize it using your preferred language:
> http://www.sqldts.com/default.aspx?301
> This is useful when the number of parameters is fairly limited. An
> alternative if you have many global variables is to put the values in a
> database table or .INI file, and use a Dynamic Properties task to retrieve
> them and assign them to the variables. This allows for more complex logic,
> conditional values based on server name, AD site name etc. Finally, you can
> assign global variable values directly in an ActiveX task.
> As for how to pass in the month/year to an INSERT, these pages may be
> useful:
> http://www.sqldts.com/?234
> http://www.sqldts.com/?205
> Simon

Simon,

Thanks a lot, very helpful!