I created a package that performs several data pump tasks
loading data from a workbook to SQL Server tables. I
have to run this package on several different servers and
several different databases on each server. The data may
be different for each database but the schema is the
same.

I've tried to script it so that I can loop through each
database instance and execute the package by changing the
source and destination data sources, but I seem to be
missing something because I can not get it to work
properly. I've tried it in the UI, and it asks me if I
want to reset all transformations using the connection I
just changed. I assume I am running into a similar
problem in this case. My question is, can I script these
data pump tasks in such a way that I can change the data
source for each connection on the fly without losing my
transformations.

Any help would be appreciated. Have a merry christmas.

Try using the dynamic properties and pass the server names and database
names as global variables. You will be able to use the DTSRUN utility to
submit the global variable values.

"Scott" <s_jungers (AT) hotmail (DOT) com> wrote

You can either call this externally using something like VB, VB.Net or C#
and perform the loop there changing the properties of the package as you go
or you can use loops internally to the package.

Here is an example

Looping, Importing and Archiving
(http://www.sqldts.com/default.aspx?246)



--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Scott" <s_jungers (AT) hotmail (DOT) com> wrote