Any help would be much appreciated!!

I am creating a generic DTS package that will export data from SQL to
Access on a daily basis. All one would do is change the database names
etc stored in global variables and run the package. The steps I have
are as follows:

1) Select table names in the SQL DB and set the rowset to a global
variable
2) Loop through each table in that rowset and pump the data into the
equivalent access table

The data pump is however not working. The first table pumps the data
fine, but when it reaches the second table in the loop it crashes as
the transformation is still looking at the first table columns. I
somehow need to set this dynamically.I have read a couple of messages
posted and it appears you have to write a script that will set the
source and destination items for each pump. What I did was set the
workflow properties of the Transform Data task to the code found on
ssqldts.com:

Dim oPkg, oDataPump
Dim sSourceTable, sDestinationTable

' Derive the new table names
sSourceTable = DTSGlobalVariables("gvSourceTable")
sDestinationTable = DTSGlobalVariables("gvDesTable")

' Get reference to the DataPump Task
Set oPkg = DTSGlobalVariables.Parent
Set oDataPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask

' Set the new values
oDataPump.SourceObjectName = sSourceTable
oDataPump.DestinationObjectName = sDestinationTable

' Clean Up
Set oDataPump = Nothing
Set oPkg = Nothing

Main = DTSStepScriptResult_ExecuteTask

It still does not work. How would I create a new transformation for
each table and make it auto map. The table names in SQL are the same
as the Access DB.

Please Help!!

Ok yes you do.

You not only need to change the connection properties but you will need to
remove all Transformation objects and then add them back again using the
metadata from your tables.

I have an example of doing this using a Query going to an Ecel spredsheet so
you could edit this and then loop through your recordset. It should be
doable so long as the destination columns match the source columns.

mail me privately if you want a look.

--
--

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


"Alex" <tigermine13 (AT) hotmail (DOT) com> wrote