Re: Generic DTS package

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Allan Mitchell (allan_at_no-spam.sqldts.com)
Date: 06/09/04 

Date: Wed, 9 Jun 2004 16:57:25 +0100

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 spred*** 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@hotmail.com> wrote in message
news:40aa650b.0406090748.e77d2b5@posting.google.com...
> 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!!
Quantcast