I am somewhat new to DTS, but am an experienced db programmer. I need one
DTS Package that will import various tables from one (non sql server) system
to the current sql server system. I am not going to be copying all tables
(about 250) but probaby about 100 of them.

So far, I have been able to setup two connections and one transformation
between them. This works great for one table but I do not want to manually
create 100 of these transforms. Is there a good way to do this multi-table
download from one (non-sqlserver) database to a sql server database? I have
found all sorts of ActiveX scripts that will manipulate a single transform,
but no examples on how to create mnay transforms between two connections.
The next step I plan to take is to put all the tables I wish to download
into a table and reference that table to loop through what is necessary to
download.

OK so this isn't as easy as you may think.

There are a couple of things you can do here is one

Use the wizard to generate a package that will move all tables to your
destination (copy-copy)
You then generate through the object model the link between the name of the
Step and the SourceObjectName in Each dataPump task so you may have
something like

DTSStep_DTSDataPumpTask_1 - Customer

You then add this information to a Database table

CREATE TABLE WhichTablesToFire
(
StepName varchar(128),
TableName varchar(128)
ShallIFire bit
)


You can then read this table into a rowset and loop over it in your package.
You disable all steps where ShallIFire = 0 and enable all steps that are
ShallIFire = 1. How you set that flag is up to you.


Another way would be to Recreate the SourceColumns and DestinationColumns
properties of the Transformation object for each table. You create a
transformation between each pairing. This would mean you know the
definition of the source up front for every table and would then need to
feed it in to your properties. This is a lot more work IMHO.





--
--

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


"Randy" <Randy (AT) nospam (DOT) com> wrote

thanks Allan, I should have been more clear. The table structures are
already there. I just need to copy the data. Does this change your
answer?

What I want to do is set up one dummy transform task and create an
ActiveX task. The ActiveX task will loop through all tables needed to
have their data copied and will change the SourceObjectName and the
DestinationObjectName of the transform task and then execute the task.
However, anytime I change the SourceObjectName and
DestinationObjectName and execute the task, Sql Server freezes. Im
not sure if this is the best way to do this

On Sat, 19 Jun 2004 06:42:28 +0100, "Allan Mitchell"
<allan (AT) no-spam (DOT) sqldts.com> wrote:

My answer does not change. If you change the Souce object and the
destination then you will invalidate the transformations. These are not
"auto-re-generated" just because we ask for different objects.


--
--

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


"Randy" <Randy (AT) nospam (DOT) com> wrote