WynApse Home Page
Home    Blog    About    Contact       
Latest Article:

My Tags:
My Sponsors:

Lifetime Member:

Running ''Safe'' DTS Packages on SQL Server to Your Host

Why I care

Way back when I started WynApse.com, I realized that I needed some sort of CMS for posting articles. Since I wanted the site to be a 'roll your own' C#/SQL Server app, I thought about it for a while and after some investigation decided upon a method that would allow me, with a bit of work, to upload all my content material into the database using DTS.

I've been doing that ever since, and the other night was adding another DTS package and must have been bored or something because I looked a bit closerat the other DTS packages that were in the 'local' folder, meaning local to SQL server on my host. I had never really looked at them seriously. They havenames such as "Rotary Backup", "Production Replication", etc. But the other night I noticed that there is a column for "Owner" and mine were listedpretty much as I'd expect, but I could see other people's names/accounts listed!

So I clicked on one of the Packages and selected "Properties" like I do with mine when I want to change what file to upload, and I could see the packagebelonging to someone else. I could not see their password, but dang, I wonder if I could run it?

Rather than try to do that, I queried my host about it. The response I got back reads:

Dear customer,

DTS is not a secure method and we do NOT support DTS. Any DTS usuage is taken at customer's own risk and precautions. We suggest you create the DTS package locally.

Oops... didn't know THAT!

So now what?

I certainly have gotten used to using the DTS Packages to upload my product, so I would really hate to go back to running DTS by hand for each file, so I had to investigate this "create the DTS package locally" stuff.

If you select an existing Package in the "Local Packages" folder on the dedicated server, you can then save that package to your system by selecting Package->Save As:

Then you have to remove the existing one, which I found a little less intuitive.

What you have to do is right-click the Package and select "Versions" at which time you'll get something like this:

From the "DTS Package Versions" form, simply hit "Delete" to delete the selected one. It refreshes showing one less version. Hit Delete again until you hit Delete on the last one and get this message:

Press "OK" then "Close", and it's gone!

Running your 'local' DTS Package

At this point, your DTS Package is no longer on the server, and from what my host has said, I think that's a good thing! But now you have to be able to execute your DTS Package from your loacl machine, so here's how to accomplish that...

Open up your database connection as usual, then right-click on Data Transformation Services, and select "Open Package". Simply browse to the place where you saved the ones you took off the server.

There are only two differences:
  • If you've saved versions, you have to drill in and pick the version to run:

  • If you want to save the version, do so with Packages->Save. If close without saving, and are prompted to Save changes, do not say "OK" because the changes will be saved back to the server and you'll have to start all over again!

Bottom line is that once you open and run it, it runs just like it did from the server.

What a relief :)
Copyright © 2006-2022, WynApse