hi all,

I have 5 source data that contain a table with exact schema, now I need to
load data from all 5 database into one staging database table.

I have created one ssis package that load data from one database, then I use
dts object on my C# code to load and modify the connection and variables
with a loop to load data from the 5 databases,
since the only difference for these data source is the database name.

now the package need to run independently in the SQL server. I try to use a
for loop with a Execute package task inside to call that package, how can I
modify the connection of the child package in the loop? or should I create a
package for every database?

thx

Kevin

Call the package using the shell application dtsrun.exe. It has to be
available on every machine that has SQL Server installed. Call this app from
your VB code. There is a VB statement for calling applications. Add the
parameters to the call of dtsrun for the database and user. Each iteration
supply the value for a global variable, e.g. gsDBName, in your package.

Ofcourse, you also have to add this global variable to your package and use
a Dynamic Properties task in the package to set the connection string of your
source database to the value in the global variable.

The dtsrun call should be something like:

"dtsrun /s myDB /u test /p test /a "gsDBName":"8"="X"

/a is used to supply glob.var. values.

Replace X by the source DB name you want to connect to.

forgot the package name:

dtsrun /s myDB /u test /p test /n "packagename" /a "gsDBName":"8"="X"

And ofcourse VB should be C#

Hello Kevin,

You say SSIS and then say DTS. Which one are you using?

Have a look at Parent configurations, in fact configurations in general.

Also look into Property Expressions

You do not need C# to do this at all.


Allan

Allan,

He said he was coding it in C#, then I called it VB. That's why I corrected
it. BUT, there is no C# in DTS, so he must be talking about SSIS so my
solution cannot work for him.

And Allan is right regarding SSIS: try to use property expressions iso. C#
script.

SO, if anyone needs Kevin's problem answered for DTS, use my comment

hi Frans and Allan,

I have created a package to call a child SSIS package, the reason I am using
the term dts is that the name DTS didn't really vanish in SSIS,
since the file extension is still .dtsx and by using the script task, I am
able to access and modify the connections and other variables in the
package.

thanks for the responses, but I do encouter other issue when load and
executing the SSIS pack using the managed dts object in C#, since some of
the tasks require higher level of edition of sql server to run e.g. the
Analysis Processing Task, but it runs fine in the vs.net IDE, when deploying
the package to the web server and running it with C# managed DTS object, it
failed.

anyway, I have post another threat here for that issue, hope someone can
clear that up for me.


thx


Kevin


"Frans van Bree" <FransvanBree (AT) discussions (DOT) microsoft.com> wrote