Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Portable DTS Packages

By Jonathan Stokes,

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

  1. Create a database and call it CONFIG.
  2. Create a table to hold the DTS system parameters
  3. Script:

    CREATE TABLE [dbo].[DTS_PARAMS] (
                 [LOGICAL_NAME] [varchar] (100 NOT NULL,
                 [PHYSICAL_NAME] [varchar] (100)  NOT NULL)
    
  4. 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.
  5. 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.
  6. 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.

  7. 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.
  8. Create an in file called dtsConfig.ini. Cut and paste this code into notepad and save.
[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.

Total article views: 11382 | Views in the last 30 days: 0
 
Related Articles
FORUM

changing the connection password hrough another package

changing the connection password through another package

FORUM

Urgent: Need Help to change server connection of SSIS packages

Need Help: For Changing server connection in SSIS for deploying into production without opening each...

BLOG

Change Package Properties Using Parameters–SSIS 2012

In DTS, changing package properties was close to impossible (the less we talk about DTS, the better)...

BLOG

SSMS 2012 || Connect to Server (Additional Connection Parameters Page)

The Connect to SQL Server Management Studio presents a new Additional Connection Parameters Page. Us...

FORUM

SQL Server stopped after changing startup parameters for mirroring.

SQL Server stopped after changing startup parameters for mirroring.

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones