Tuesday, March 20, 2012

Can I copy a DTS Package?

The scenario:
9 db tables populated by 9 Excel Import Files via DTS.
Will I need to create a DTS package for each import? Columns are identical in all 9 - the only thing different is the destination table name and source file name.

I've had to map over 80 columns using DTS and don't want to do it for each instance!

Any help would be appreciated..1. Rightclick the DTS-package in Enterprise Manager
2. Choose "design package"
3. Make the changes you want
4. Go to menuitem "Package" and choose "Save as"
5. You now have a copy of your DTS-package|||Nice one! Cheers.|||This is not the best way -
Dude - create a table in the destination db called tblFileSource that has:
ID, SourceFile, DestinationTable, importDate

Define FileSource and DestinationTable as Global Variables in the package.
Then, the first step of you package use a Execute SQL task that will set you global Varaiables to the result of

select top 1 SourceFile, DestinationTable
from tblFileSource
where importDate is null


Then, use a Dynamic Properties Task to change the source and destination in the Data Transformation Step.

Then, after the Transformation, do another Execute SQL Task (on Success):


Update tblFileSource
Set importDate = getDate()
where SourceFile = ?

Where the ? is the global Variable FileSource.

Isn't this a more professional method - comes in handy when the number of files increases.

No comments:

Post a Comment