Post reply

DTS package script

  • February 23, 2005 at 12:59 pm

    #85106

    Hi

    I have to download data from AS400 to SQL server. For this purpose I have a DTS package on my local SQL Server. I need to send the package to other people to perform the same activity on their machines. They are in a different geographical location (this rules out my going there personally)

    1. Can I generate a script of DTS package so they can run it?

    2. Can DTS package be executed from command prompt? Or from SQL Query Analyzer?

    3. What if they do not have destination database setup? Can I create a new destination database through DTS when specifying destination tablename?

    4. Or is there a better way of doing this?

    Thanks

  • February 24, 2005 at 5:08 am

    #543875

    Couple options that may help....

    Export the DTS Package as a Structured Storage file and give them instructions on how to open it and execute it.

    Or, Save the package as a VB module. Modify the contents to suite your needs.  Compile the .bas file to a .exe and distribute it.

    To handle the creation of the new table if it does not exist, simply generate the script in Ent. Mgr. and select the generate drop statement option.  Modify the script to perform an:

    IF NOT EXISTS( select .. from sysobjects... name = N'Tablename' .....

        CREATE TABLE ......

    add an Execute SQL Task to run the above at the package start.

    -Mike

  • February 24, 2005 at 8:07 am

    #543928

    ...and it's possible tu run it from command prompt. It's clearly explained in BOL "how to execute a DTS package using dtsrun"

  • February 24, 2005 at 12:32 pm

    #543997

    1. Use a dynamic variable task to set up connection parameters at run-time for the destination.

    2. Save the package as a structured storage file

    3. To help you generate the command line arguments, use the tool : dtsrunui (in the 80\binn\tools directory of your sql server installation). This is a graphical tool that helps you pick the package and the global variable values and generates for you the command line version that will be using dtsrun.

    4. beware of package versions. If you need to always run the last version, just take out the GUID of the package and use only the package name.

    HABIB.

     

     

    Kindest Regards,

    Habib Zmerli (MVP)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply