Portable DTS Packages with built in resilience.
Unless a company never changes server names,
does not utilise a test environment or never performs server migrations etc,
there will be occasions when DTS packages require amendments in order to continue
to function. When a dependent object of a package changes, such as a connection
to another server, a failure can usually be tracked down to the renaming of
that object. In order to keep these packages free from failure, other than
making these amendments manually package by package, there are various ways to
get around this problem so long as you have a built in solution already
integrated into those packages.
The purpose of this article is to be able to create typical DTS packages that draw configuration parameters from a central location, enabling changes to any package dependent objects to be implemented using a singular update. By definition, these packages will be able to:
a) Function on a renamed server.
b) Handle changes to connection to databases on other servers and changes in those server names.
c) Handle changes of import and export files and their locations.
d) Function seamlessly in a disaster recovery scenario.
e) Be executed from a client machine as well as on the server.
A way to achieve this goal is to use an ini file to hold all of the parameters. Whilst this achieves most of the goals, it makes life a little harder when it comes to administration. Also, if you plan to use the ini file on more than one server, any change will affect all the servers.
The ini file physical location is another
issue of contention. Remember that when executing DTS packages a reference to
the c: drive will be the c: drive of the client executing the package – Not the
server where the package resides. Hence, to place the ini file on a local
drive, an identical copy must be maintained on the server and all the clients
executing packages. By specifying a computer name / drive for the ini file location,
you are defeating the object by having to change each package to reference the
new location.
A better way is to store object definitions
in a simple two column table. To change a definition of an object a simple
update statement is all that is required. This example combines both approaches into
an effective deployment and administration plan. There is a specific reason
that the ini file method must be used, which I will explain further on.
As we need to need to use an ini file, I
recommend mapping a free drive to the location of the ini file so that clients
and the server all use the same ini file, giving a single point of
administration. Another benefit accrued from this method is the reduction in
the chances of error. It is much harder to synchronise a bunch of ini files
than ensuring that a client and server is mapped to the same place. If the
drive mapped changes, DTS packages will fail rather than error.
Initial Settings
Follow these initial steps to set up a DTS system parameter table to hold all of our object definitions and an ini file to hold the server name that the package resides on:
DATABASE OBJECTS
- Create a database and call it CONFIG.
- Create a table to hold the DTS system parameters
- Put a primary key on the LOGICAL_NAME column. This column will hold a made up name for an object whereas the PHYSICAL_NAME will hold the actual name. Therefore, we can have different logical names pointing to the same physical name.
- Insert some rows into the table for
testing purposes. In this case three rows for dev, test and live. For
example, this assumes we have a server called TESTSERVERNAME in our server
collection that we informally call TEST. It can be replaced by the real
name for the test server.
INI FILE
To create a connection the ini file, map a drive to a share on your network. In this case we will use the root of a Z: drive. You can map this to any share as long as you use the drive letter Z.
- Create an in file called dtsConfig.ini. Cut and paste this code into notepad and save.
Script:
CREATE TABLE [dbo].[DTS_PARAMS] ( [LOGICAL_NAME] [varchar] (100 NOT NULL, [PHYSICAL_NAME] [varchar] (100) NOT NULL)
Script:
INSERT INTO DTS_PARAMS VALUES ('TEST','TESTSERVERNAME')
INSERT INTO DTS_PARAMS VALUES ('LIVE','LIVESERVERNAME')
INSERT INTO DTS_PARAMS VALUES ('DEV','DEVSERVERNAME')
INSERT INTO DTS_PARAMS VALUES ('FOLDER','FOLDERNAME')
The first three entries are self explanatory and the last entry will be used as our import/export folder. Where possible imports should always be made on file copies rather than originals as most files cannot be accessed by DTS when already open. Similarly, exports should be put somewhere before overwriting originals and then copied across.
[Server Name] value=YOURSERVERNAME
Replace the server name with your own server name.
The Package Template
This example creates a package template, which you can integrate into existing packages or use as a base for creating new ones.
1. Create an empty DTS package and save it as dtsTestpackage.
2. Within the package, create a connection to the server that the DTS package resides on and where you have created the new database and table. Name it ‘Local Connection’ and enter either windows or sql server passwords depending upon authentication required. Set the connection to point to the newly created database ‘CONFIGS’ and the table DTS_PARAMS.
3. Click on the Dynamic Properties Task. It looks a little like a puzzle - A small grey box with a lollipop sticking out of the top and two matching, protruding lollipops on the left hand side. Name it ‘Set parameters’. Your package should look like this:
4. Double click the Set Parameters Dynamic Properties Task.
5. Click on the ‘Add’ button and expand the Connections Node
6. On the right hand side you will see various parameters for the connection to the local server. The one we are interested in is the ‘DataSource’, which should be displaying your server name. This is the parameter we are going to reference in our parameter table. Double click on the server name and from the source dropdown, select “INI FILE”.
Browse to find the dtsConfig.ini on the Z drive. Select Server Name as the Section and Value as the key. Clicking on refresh will show your server name.
To test it out, right click on the ‘Set Parameters’ task and click on Execute step. If you change the value in the ini file, the connection will change accordingly.
Wherever this package is now run from, the name of the data source will always be the server name stated in this single ini file. The reason we need to use an ini file is that we have to have a connection in the DTS package in order to select from our DTS parameters table. If we use a connection with a hard coded server name, the package is no longer portable. Unfortunately, there is no way to retrieve the server name that a package resides on; Environment variables always reference the client.
You now have a template DTS package that all other packages should be based upon. A small overhead that will save a lot of time in the future as well as offering more resilience.
Real World Example
1. Create two basic connections to different servers. In this example we use one for TESTSERVERDEV, which should be named DEV and TESTSERVERLIVE, which should be named LIVE. It does not matter which server or database you connect to as the connection will be overridden anyway. Your package should now look like this.
2. Double click on the Set Parameters object, click
on Add and open the Connections node again as before. You will now see 3
connections in your package. Highlight the connection DEV.
3. Double click on the DataSource in the right hand panel and dropdown the Source to point to Query. Type in the following in the query panel. Point the connection to “Local Connection”
Script:
SELECT PHYSICAL_NAME FROM CONFIG.DBO.DTS_PARAMS WHERE LOGICAL_NAME = 'DEV'
4. In the above snapshot, click on the Refresh button and the preview box should show DEVSERVERNAME.
5. Repeat the same process for the live connection
from the connection node, but replace DEV in the query with LIVE
Script:
SELECT PHYSICAL_NAME FROM CONFIG.DBO.DTS_PARAMS WHERE LOGICAL_NAME = 'LIVE'
The refresh button should preview LIVESERVERNAME. You should end up with the following screen:
Running the Package Parameter Task
To test, right click on the Set parameters Object and choose ‘Execute Step’. If you now view the parameter of each connection by double clicking on it, it will be set as per the reference to the corresponding query.
Place the ‘Set Parameters’ task as the first step in all your packages with On Success links to everything else after. Put them in a little box in the corner to keep things tidy. For this particular package example, create a DTS pump task between the live and dev connections and try it out.
Summary
Referring to my initial promises, they have all been met:
Function on a renamed server – Works because we always reference the single ini file.
Handle changes to databases on other servers and changes in those server names – By referencing parameter table for server name.
Handle changes of import and export files and their locations – This works in the same way. In order to reference a file we can use the same query and add the filename as a suffix:
Script:
SELECT PHYSICAL_NAME + ‘\Your filename’ FROM CONFIG.DBO.DTS_PARAMS WHERE LOGICAL_NAME = ' FOLDER '
Be executed from a client machine as well as on the server – Does not reference any local files or connections.
Function seamlessly in a disaster recovery scenario. – OK. This is not quite true in the scenario I have shown. However, if you use the server DNS name instead of the physical name this goal is accomplished.
One Step Further
As well as setting server names, we can drill to a lower level of granularity and also set a Database Name. This is marked as ‘Catalog’ on the screen that you set data source name in. All in all, for a small amount of work required to add one simple connection and an ini file, any changes can be facilitated easily. The DTS_PARAMS table can also be stored on all the servers if required. It’s a good idea to have it in a separate database so it is quick to restore if need be.
The packages are now more resilient, portable and easily deployed in a test environment with a minimum amount of administration overhead.
