Being Mark Cohen

Sometimes you have to be able to work on big ugly DTS packages and move them between servers.

Some pointers

• If your source databases change names like the Espresso ones do then you need to use select statements for your data sources instead of choosing table names from the dropdown for the sources.
• If your destination databases change names then you should throw the towel in and use something other than DTS.
• If you have cross-database queries you will have to fully qualify table references with database.owner.table and you can’t make this dynamic using an execute SQL task (at least not that I could see) so you have to write some good old VBS to use ADO to execute SLQ strings. You can then read ini file settings into global variables as below.

Read on for more details

Here’s the low-down (you will need to dust off your vb script brain cells

First, you define an old fashioned ini file. Ideally put it in a location that matches on all your environments’ desktop, dev server, staging and production. Otherwise you have to reconfigure on each deploy which is exactly what we are trying to not do.

Here’s a simple snippet from an ini file:

[systemname]
Server=ServerName
Database=DatabaseName
User=username
Password=xxxxxxx

Dynamic Properties

Once you have set up your ini file you need to be able to map the values to your connections etc. To do this you add one of these to your DTS package:

<%image(20050616-dtsicon1.Jpeg|40|40|Dynamic Properties Icon)%>

It’s called a ‘Dynamic Properties Task’. You then open it up and you get this window:

<%image(20050616-dts1.Jpeg|463|486|DTS dynamic property task - screen)%>

Click Add, and you get another funky window:

<%image(20050616-dts2.Jpeg|620|486|DTS Dynamic properties screen - add items)%>

Expanding connections you’ll get your feed connections and you can then map any field on the right to the ini file by double-clicking it. The rest is self-explanatory.

Global variables

To define global variables, just right-click on the package and select package properties and then go to the global variables tab.
To set them, do as above, but expand the global variables node on the left.
To read global variables in VBS use this:
myLocalVariable = DTSGlobalVariables(“myGlobalVariable”).Value

VBS

Here’s a snippet of how to execute a query in vbs for those who are as rusty as me.