Migrating DTS packages from Dev to Test to Prod

Question

Migrating DTS packages from Dev to Test to Prod

supersqlserver

Grasshopper

Points: 21
More actions
November 10, 2003 at 8:16 pm

#106550

Here is what I have, DTS packages developed and currently running using EM. We have Development and Test environment on one SQL server and one SQL server box for production. On dev/test SQL box I have alias setup for database connection which point to either dev or test database. DTS packages use this alias. So when I migrate DTS packages to production I do not need to change these packages but I simply change alias (outside DTS package) to point to prod server. This works great when I am working either on dev or test env. I run into prob when I want to execute a DTS package both in dev and test env at same time.. What is the best approach to handle this? What are the best practices for migrating DTS packages from dev to test to prod environment without changing DTS package?
Thanks for your help !

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

jordanac Hall of Fame Points: 3653 More actions · Answer 1

Hi,

My method of automating the moving of SQL 2K DTS packages might not be the best way but it works for me. I inherited servers where the developers used hundreds of DTS pakages - yuk-

I use dbo.sp_enum_dtspackages to get the latest version id's in a table.

I then join to the msdb.dbo.sysdtspackages with the new table and create a table that will be used to insert into the target servers msdb.dbo.sysdtspackages.

I am very busy with a consolidation project and dont seem to find the time to find a less "hacker" way. Be very careful when inserting into msdb.dbo.sysdtspackages. Make backups before doing anything.

If it aint broke don't fix it!

Andy.

philcart SSC-Forever Points: 47794 More actions · Answer 2

Probably the best approach to making your DTS packages portable between each environment is to make all the environment dependant settings as dynamic as possible.

The way I achieve this is to have a 'DBA Only' database on each of the servers. This database contains a table that provides the server connection settings for each server that the DTS package will connect to, Servername, database, login details, etc...

When the package is executed it is passed the servername, database and login details to connect to this table. Then in an ActiveXScript task I use an ADO connection to retrieve the details and iterate through all the server connections and set them appropriately.

When you migrate the package from one environment to the next all you need to do is ensure the values in the table are correct and alter the calls to the DTS packages accordingly.

You could probably achieve the same approach using INI files and a Dynamic Properties task, but I've never really been a fan of INI files.

I've also used this approach to dynamically set things like the package name for the ExecutePackage task, database, table and Input file properties for the DataPump and BulkInsert tasks.

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

data_czar Ten Centuries Points: 1147 More actions · Answer 3

I use two tasks in every DTS Package I create to control at runtime the environment settings (1 activex and 1 dynamic properties task). Then when I move from DEV to TEST I just have a manually change the value of a global variable (gvEnvironment).

The activex script contains the following sample code:

=======

Function Main()

Select Case DTSGlobalVariables("gvEnvironment").Value

Case "DEV"

DTSGlobalVariables("gvDestinationServer").Value = "DEV_Server"

DTSGlobalVariables("gvDestinationDB").Value = "Dev_DB"

CASE "TEST"

DTSGlobalVariables("gvDestinationServer").Value = "TEST_Server"

DTSGlobalVariables("gvDestinationDB").Value = "Test_DB"

CASE "PROD"

DTSGlobalVariables("gvDestinationServer").Value = "PROD_Server"

DTSGlobalVariables("gvDestinationDB").Value = "Prod_DB"

CASE "FAILOVER"

DTSGlobalVariables("gvDestinationServer").Value = "Failover_server"

DTSGlobalVariables("gvDestinationDB").Value = "Failover_DB"

END Select

'Build other Globel Variables as needed....

'Global Variable to be used in a Transform Data Task

DTSGlobalVariables("gvDestinationTable").Value = "[" & Cstr(DTSGlobalVariables("gvDestinationDB").Value) & "].[DBO].[table_name]"

END FUNCTION

=======

Then I follow this with a Dynamic Properties Task to change the properties of tasks that are impacted by environment changes.

It may seem like a lot upfront, but since I use the same servers most of the time, it is really a lot of cut and paste. This process has saved my many hours of senseless debugging when migrating to another server. (Remember to change the error log and exception files locations..)

philcart SSC-Forever Points: 47794 More actions · Answer 4

Using UDL files can be a security risk. If you change the extension of the UDL file to TXT, you can read the connection string in plain text. Not very desirable if you use a username and password to connect to the server.

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface