Results 1 to 10 of 10

Thread: Dts

  1. #1
    Join Date
    Dec 2004
    Posts
    11

    Dts

    Hi There,

    Is there a way to generate a script out of a complex DTS package so that the script can be sent to the client and when they run it, it’ll create the DTS package for them? I tried to work with Structured Storage Files, but it still needs some configurations, and I am not sure that client would be happy with set of instructions…
    If anyone has experience with that or can point a web site to get help, it’ll be appreciated it.

    Thanks,

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    You can save dts package as vb file in sql2k.

  3. #3
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    you should create a DTS package in such a way that everything is dynamic - (using dynamic properties and global variables). then send the dts package to your client as a structured storage file and give one small instruction document on how to execute the DTS package passing values to global variable.

    If they want the DTS package to reside on a server, they can restore a DTS package from a structured storage file and save it. For executing the DTS package give one small instruction document on how to execute the DTS package passing values to global variable

  4. #4
    Join Date
    Dec 2004
    Posts
    11

    Thanks

    Thanks both to rmiao and MAK for their suggestions. I'll try and see if I can manage to accomplish it.
    Cheers,

  5. #5
    Join Date
    Dec 2004
    Posts
    11
    I've managed to make structured storage file to be able to work with small instructions in a client environment. However, when I ran a script which schedules a job for this specific dts package, it keeps failing. I think because the .dts file is doing the job when it's executed through a batch file, but it never really appears on the Enterprise Manager. Then, the job is not able to find this .dts file (package on the server).
    Is there any way to make it work?
    Thanks,

  6. #6
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    try giving UNC path like \\servername\sharename\xxxxx.dts

  7. #7
    Join Date
    Sep 2002
    Posts
    5,938
    And ensure sql agent service account has proper permission on remote files. By the way, possible to load and save package in sql server on your client site?

  8. #8
    Join Date
    Dec 2004
    Posts
    11
    Thanks again for your responds.
    MAK, I couldn't make it work with UNC path, but I think because I don't do it right. I'll try again tomorrow morning, after reading some stuff tonight. It may help.
    rmiao, I don't see any options for SQL Agent Service account permissions for remote files, but I'll look into it later.
    No, I don't have an access for sql server in the client site. I created DTS package in my local SQL Server, and trying to implement it in another SQL Server (I'm connecting remotely to the machine) and assuming the second one is the client...
    Cheers,

  9. #9
    Join Date
    Dec 2004
    Posts
    11
    Things are working fine now Thanks again for the help in the past couple of days.

  10. #10
    Join Date
    Sep 2002
    Posts
    5,938
    To access remote file in sql job, have to start sql agent service with domain account.

Posting Permissions

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