Workshop : Configurable DTS Packages
I finished my first “workshop” article!
Had a bit of free time and decided to finish it as it’s something that I’ve been meaning to do for quite a while now.
As an aside, the “workshop” series of articles are meant to be my
gesture of giving back to the community from which I’ve taken so
much. I know I’ve found tons of useful blog posts and articles
online that have helped me greatly in the past, so I feel that it’s
only fair that I give back a bit. I don’t claim to be some guru
or know all of the answers,
but my hope is that the information that I provide in these articles
can help someone out there get their job done more easily. I’m
also aware that someone out there may have already done something like
this, but my own experience tells me that it’s always helpful to work
with various sources to gain different insights.
Now onto the article!
In March, I worked on creating a SQL Server DTS package to import data
from various data files. One aspect that grew tiring really fast
was having to manually change the paths of the source data files each
time I moved the package from one environment to another (or even from
my machine to Kent’s). We had 6 datafiles at the time (early
testing/development phase) in addition to various other settings that
were environment dependent like mail server addresses and database
logins. I was simply too lazy to keep fiddling
with the settings in DTS every time I had to send the package to the
client or to Kent.
The solution I came up with makes use of DTS global variables, the
powerful “Dynamic Properties Task” task, and some simple VBScript.
Still interested? Then read the full article.
Feel free to leave comments, questions, and criticisms 🙂 in this thread
I liked your site.
I need to make an automated script to transfer no. of rows from one table to another table using parameterized way. Dynamically assign Source and Destination servers,databases and tables. Is there any way we can build the script for this?