Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004

    Unanswered: 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..

  2. #2
    Join Date
    Jul 2003
    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

  3. #3
    Join Date
    Oct 2004
    Nice one! Cheers.

  4. #4
    Join Date
    Oct 2004
    Durban - South Africa

    Correct Method

    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.
    If at first you don't succeed, call it version 1.0

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts