Can the transformations be changed "dynamically" during runtime? I plan
to set up a single package with two SQL server connections (source and
destination) with global variables (using dynamic properties task) for:

1) source server
2) target server
3) stored procedures (runs on the source server)
4) destination table

All of bove can be handled but I am not sure about dyanmically changing
the transformations (column mapping) as the stored proc1 has a different
column mapping than stored proc 2and so on.

The goal is write a single DTS package that can handle multiples stored
procs going to multiple target tables. If the pkg is set up, I'll use
dts run utility to call it and pass one stored proc and one traget table
at a time and so on. I am using SQL server 2000.

Thanks for your help!

Hello Test,

No, this really doesn't work with the SSIS model. SSIS wants to verify the
sanity of the package by comparing metadata to metadata. Sounds like what
you really should be looking at is using SqlBulkCopy in the .NET framework
instead.

I can elaborate more on that later if you like.

Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

Thanks, Kent!

I changed the model not to use the transformations. Instead, I created a
new package that has only one task, which is ActiveX VB script task, to
control opening ADO connections, connecting DB servers, DB and executing
SPs. The concept is:

insert into @server1.@database1.dbo.@table1
exec @server2.@database2.dbo.@stored proc1

The package is working fine with the hard coded DB parameters values set
up inside the package. The goal is to make this single package work for
any @source and @target server and table. In order to make it dynamic,
I set up a Dynamic Properties Task to this package to declare my global
variables (server, db, table, sp). However, I do not seem to find what
properties I need to associate with for these global variables. (For
every global variable you set up, you need to assign it some peoperty,
right?. what I do in this case?)

I'll be executing this package form the job script (using xp_cmdshell)
as I need to call this pkg over and over and change the DB parameters at
runtime. Here is an example:

declare @dts varchar(1000)
set @dts = 'dtsrun /F d:\MyDTS.dts /N MyDTS /A gvTargetServer:8=' +
@@servername + /A gvSourceServer:8 = 'Some_Source_Server' + /A gvSP:8 =
'exec Some_stored_proc 1'
exec master.dbo.xp_cmdshell @dts