DTS Environment Control



What solutions have other worked up to handle DTS packages and promotions through their different environments?



In our scenario we have a 4 tier environment (Dev/Test/PreProd/Prod). The challenge is when you promote your packages up; the connections have to be changed to reflect the new environment. For simple packages it's not too big of deal - but for more complete ones - you can spend an hour carefully changing all your connections. Try promoting a package that imports/exports 200 tables that was created with the wizard - there is a separate connection for every table.



I had one solution where we would add an ActiveX script task as the very first step, that would determine what SERVER the package was executing from - and based on that would dynamically change all the connections by looking up the package name and server from a DB. Of course that was hairy to use because when you develop your DTS package and run it, it is running in the context of YOUR machine, despite being saved on a different server. The environment control solution I developed was really only useful when the package was ran as a scheduled job - as then it was truly running as a job on one of the 4 tier environments. The DBA's shot my solution down as it required that I keep the batch login user names and passwords in my control table that was used to change the connections based on the environment the package was running from.



The other problem is source control - we use VSS. If I have a package called XYZ that grabs files from an FTP location every morning - my XYZ package for DEV will look different than the XYZ package for TEST. I could pre-pend the environment in front of the package name and have DEV-XYZ, TEST-XYZ, PREPROD-XYZ, PROD-XYZ - but then imagine that we decided to add one more step to message the data further or pick up more files within the same package - I would have to make the changes to 4 different version of the package called XYZ - or make it to one and re-save it across the environments and change the connections for each environment.



Anyone have better solutions?

You are right it can be hairy.

I have the same environments as you so I do this.

I know I have machines which will execute packages in a given environment.
I am about the only person that will execute packages in all 4. I like to
make aliases to my SQL servers using CliConfg. This way I do not need to
ever change them again after I have built my packages. If i need other
information like The XL file for production location I can store that in a
table on the aliased server and retrieve it through a DP task.

This means I need to make sure that the boxes that will call my packages in
each environment have aliases set up. The users will not have permissions
onto environments which they shouldn't.

In your 200 table wizard generated package you could simply use the object
model to change the Data Source properties to an Aliased Server then save
the package back out.

HTH

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"Joe Horton" <horj235 at lni dot wa dot gov> wrote

DTS Environment Control



What solutions have other worked up to handle DTS packages and promotions
through their different environments?



In our scenario we have a 4 tier environment (Dev/Test/PreProd/Prod). The
challenge is when you promote your packages up; the connections have to be
changed to reflect the new environment. For simple packages it's not too
big of deal - but for more complete ones - you can spend an hour carefully
changing all your connections. Try promoting a package that imports/exports
200 tables that was created with the wizard - there is a separate connection
for every table.



I had one solution where we would add an ActiveX script task as the very
first step, that would determine what SERVER the package was executing
from - and based on that would dynamically change all the connections by
looking up the package name and server from a DB. Of course that was hairy
to use because when you develop your DTS package and run it, it is running
in the context of YOUR machine, despite being saved on a different server.
The environment control solution I developed was really only useful when the
package was ran as a scheduled job - as then it was truly running as a job
on one of the 4 tier environments. The DBA's shot my solution down as it
required that I keep the batch login user names and passwords in my control
table that was used to change the connections based on the environment the
package was running from.



The other problem is source control - we use VSS. If I have a package
called XYZ that grabs files from an FTP location every morning - my XYZ
package for DEV will look different than the XYZ package for TEST. I could
pre-pend the environment in front of the package name and have DEV-XYZ,
TEST-XYZ, PREPROD-XYZ, PROD-XYZ - but then imagine that we decided to add
one more step to message the data further or pick up more files within the
same package - I would have to make the changes to 4 different version of
the package called XYZ - or make it to one and re-save it across the
environments and change the connections for each environment.



Anyone have better solutions?

So what sort of alias names do you use - I guess I'm sort of confused how to
use it.

Let's say my servers just happened to be named by environment, so I would
have SQL boxes called: DEV, TEST, PREPROD, and PROD.

How would I set up my aliases?


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

We use the Dynamic Properties task for this. Each job starts with a "Set Servers" dynamic properties task that sets the "Data Source" property for each connection and most of the properties are set from an INI file in a location that's got the same filespec on each of our Test/Prod systems. On a test system, the INI file contains a value called "DatabaseServer" that points to the Test database server. On a production system, the INI file contains a value called "DatabaseServer that points to the Production server. When run from the production environment, the production server is accessed and when run from the test environment, the test server is accessed. This works nicely both for WTS terminal sessions and for batch jobs.

We've thought about more complicated schemes involving ActiveX script tasks (and we do use those for some things) but for Test/Prod, the Dynamic Properties task seems to be about all we need and it's simple.

Actually, there's one more component... We do some Execute Task functions and we also have some batch jobs that don't involve packages. These we mostly control with environment variables and command-line substitution (although we also use INI files for a few things).

"Joe Horton" <horj235 at lni dot wa dot gov> wrote

DTS Environment Control



What solutions have other worked up to handle DTS packages and promotions through their different environments?



In our scenario we have a 4 tier environment (Dev/Test/PreProd/Prod). The challenge is when you promote your packages up; the connections have to be changed to reflect the new environment. For simple packages it's not too big of deal - but for more complete ones - you can spend an hour carefully changing all your connections. Try promoting a package that imports/exports 200 tables that was created with the wizard - there is a separate connection for every table.



I had one solution where we would add an ActiveX script task as the very first step, that would determine what SERVER the package was executing from - and based on that would dynamically change all the connections by looking up the package name and server from a DB. Of course that was hairy to use because when you develop your DTS package and run it, it is running in the context of YOUR machine, despite being saved on a different server. The environment control solution I developed was really only useful when the package was ran as a scheduled job - as then it was truly running as a job on one of the 4 tier environments. The DBA's shot my solution down as it required that I keep the batch login user names and passwords in my control table that was used to change the connections based on the environment the package was running from.



The other problem is source control - we use VSS. If I have a package called XYZ that grabs files from an FTP location every morning - my XYZ package for DEV will look different than the XYZ package for TEST. I could pre-pend the environment in front of the package name and have DEV-XYZ, TEST-XYZ, PREPROD-XYZ, PROD-XYZ - but then imagine that we decided to add one more step to message the data further or pick up more files within the same package - I would have to make the changes to 4 different version of the package called XYZ - or make it to one and re-save it across the environments and change the connections for each environment.



Anyone have better solutions?

In your Dev environment you have a source of Dev1 and a destination of Dev2.
In your Test environment they are called Test1 and Test2. etc etc

You could use Alias names of Source and Destination

On the PCs that will execute the packages in each environment you set up the
aliases to point to the correct server. This means they resolve at runtime
to the correct servers. At design time you create the aliases on your box
and use them in the Connection properties. You never need to change them in
the package.



--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"Joe Horton" <horj235 at lni dot wa dot gov> wrote

On thin client implementations this is also an approach I take as well
although aliasing works and is less visible to the server admins <g>


--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"DHatheway" <dlhatheway (AT) mmm (DOT) com.nospam> wrote

We use the Dynamic Properties task for this. Each job starts with a "Set
Servers" dynamic properties task that sets the "Data Source" property for
each connection and most of the properties are set from an INI file in a
location that's got the same filespec on each of our Test/Prod systems. On
a test system, the INI file contains a value called "DatabaseServer" that
points to the Test database server. On a production system, the INI file
contains a value called "DatabaseServer that points to the Production
server. When run from the production environment, the production server is
accessed and when run from the test environment, the test server is
accessed. This works nicely both for WTS terminal sessions and for batch
jobs.

We've thought about more complicated schemes involving ActiveX script tasks
(and we do use those for some things) but for Test/Prod, the Dynamic
Properties task seems to be about all we need and it's simple.

Actually, there's one more component... We do some Execute Task functions
and we also have some batch jobs that don't involve packages. These we
mostly control with environment variables and command-line substitution
(although we also use INI files for a few things).

"Joe Horton" <horj235 at lni dot wa dot gov> wrote

DTS Environment Control



What solutions have other worked up to handle DTS packages and promotions
through their different environments?



In our scenario we have a 4 tier environment (Dev/Test/PreProd/Prod). The
challenge is when you promote your packages up; the connections have to be
changed to reflect the new environment. For simple packages it's not too
big of deal - but for more complete ones - you can spend an hour carefully
changing all your connections. Try promoting a package that imports/exports
200 tables that was created with the wizard - there is a separate connection
for every table.



I had one solution where we would add an ActiveX script task as the very
first step, that would determine what SERVER the package was executing
from - and based on that would dynamically change all the connections by
looking up the package name and server from a DB. Of course that was hairy
to use because when you develop your DTS package and run it, it is running
in the context of YOUR machine, despite being saved on a different server.
The environment control solution I developed was really only useful when the
package was ran as a scheduled job - as then it was truly running as a job
on one of the 4 tier environments. The DBA's shot my solution down as it
required that I keep the batch login user names and passwords in my control
table that was used to change the connections based on the environment the
package was running from.



The other problem is source control - we use VSS. If I have a package
called XYZ that grabs files from an FTP location every morning - my XYZ
package for DEV will look different than the XYZ package for TEST. I could
pre-pend the environment in front of the package name and have DEV-XYZ,
TEST-XYZ, PREPROD-XYZ, PROD-XYZ - but then imagine that we decided to add
one more step to message the data further or pick up more files within the
same package - I would have to make the changes to 4 different version of
the package called XYZ - or make it to one and re-save it across the
environments and change the connections for each environment.



Anyone have better solutions?

Doesn't this approach still mean that I have to change all my connections as
they move up the environments?

In my example below, as I promote my packaged from DEV to TEST - If I'm
understanding your approach, I would still need to change the Connections
for step #1 and the UNC path connections for step #3.

Dev Environment:
1) Connection Source to SQL DEV Box
2) Transform Task to a UNC file share on the dev box - transforms 1 table to
text
3) Text Destination UNC path

Test Environment:
1) Connection Source to SQL TEST Box
2) Transform Task to a UNC file share on the test box - transforms 1 table
to text
3) Text Destination UNC path

Now if we used the aliasing sort of like a DSN connection - I could simply
call my connection SQLSource in all my packages - but the SQLSource Alias on
Dev would point to dev, on test SQLSource would point to my Ttest
environment and so on...I guess I'm missing why we would use a source called
Dev1, Test1, etc?


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

I could see this approach working well for defining just SQL Source/Destination settings - but what about other sources and destinations? Say for example you had to FTP flat text files over to a Data Warehouse server - your INI files would work well for the SQL boxes - but we need to add entries for each of the flat file connections for EACH table in your INI file as well. If there were 200 tables - you would need 200 entries just for that part of the connections - just for this one package!
"DHatheway" <dlhatheway (AT) mmm (DOT) com.nospam> wrote

We use the Dynamic Properties task for this. Each job starts with a "Set Servers" dynamic properties task that sets the "Data Source" property for each connection and most of the properties are set from an INI file in a location that's got the same filespec on each of our Test/Prod systems. On a test system, the INI file contains a value called "DatabaseServer" that points to the Test database server. On a production system, the INI file contains a value called "DatabaseServer that points to the Production server. When run from the production environment, the production server is accessed and when run from the test environment, the test server is accessed. This works nicely both for WTS terminal sessions and for batch jobs.

We've thought about more complicated schemes involving ActiveX script tasks (and we do use those for some things) but for Test/Prod, the Dynamic Properties task seems to be about all we need and it's simple.

Actually, there's one more component... We do some Execute Task functions and we also have some batch jobs that don't involve packages. These we mostly control with environment variables and command-line substitution (although we also use INI files for a few things).

"Joe Horton" <horj235 at lni dot wa dot gov> wrote

DTS Environment Control



What solutions have other worked up to handle DTS packages and promotions through their different environments?



In our scenario we have a 4 tier environment (Dev/Test/PreProd/Prod). The challenge is when you promote your packages up; the connections have to be changed to reflect the new environment. For simple packages it's not too big of deal - but for more complete ones - you can spend an hour carefully changing all your connections. Try promoting a package that imports/exports 200 tables that was created with the wizard - there is a separate connection for every table.



I had one solution where we would add an ActiveX script task as the very first step, that would determine what SERVER the package was executing from - and based on that would dynamically change all the connections by looking up the package name and server from a DB. Of course that was hairy to use because when you develop your DTS package and run it, it is running in the context of YOUR machine, despite being saved on a different server. The environment control solution I developed was really only useful when the package was ran as a scheduled job - as then it was truly running as a job on one of the 4 tier environments. The DBA's shot my solution down as it required that I keep the batch login user names and passwords in my control table that was used to change the connections based on the environment the package was running from.



The other problem is source control - we use VSS. If I have a package called XYZ that grabs files from an FTP location every morning - my XYZ package for DEV will look different than the XYZ package for TEST. I could pre-pend the environment in front of the package name and have DEV-XYZ, TEST-XYZ, PREPROD-XYZ, PROD-XYZ - but then imagine that we decided to add one more step to message the data further or pick up more files within the same package - I would have to make the changes to 4 different version of the package called XYZ - or make it to one and re-save it across the environments and change the connections for each environment.



Anyone have better solutions?

No .

In the pakcgaes the connectionnames for the server stay the same. It is the
aliases through Cliconfg that change in the environments.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"Joe Horton" <horj235 at lni dot wa dot gov> wrote

When I have many tasks to set, I first use the Dynamic Properties task to set a few package Global Variables based on the .INI file (and, by the way, there's one on my desktop PC that has the "Test" settings in it) and then I write an ActiveX script task that will loop through the package tasks and set the appropriate properties to a value derived from the values in the Global Variables.

You might also have to do a little extra ActiveX scripting to enable/disable tasks dynamically (that way you shouldn't have to draw all those little dependency lines from the prep tasks to the data-transfer tasks). We've got a package or two that does this.

"Joe Horton" <horj235 at lni dot wa dot gov> wrote

I could see this approach working well for defining just SQL Source/Destination settings - but what about other sources and destinations? Say for example you had to FTP flat text files over to a Data Warehouse server - your INI files would work well for the SQL boxes - but we need to add entries for each of the flat file connections for EACH table in your INI file as well. If there were 200 tables - you would need 200 entries just for that part of the connections - just for this one package!
"DHatheway" <dlhatheway (AT) mmm (DOT) com.nospam> wrote

We use the Dynamic Properties task for this. Each job starts with a "Set Servers" dynamic properties task that sets the "Data Source" property for each connection and most of the properties are set from an INI file in a location that's got the same filespec on each of our Test/Prod systems. On a test system, the INI file contains a value called "DatabaseServer" that points to the Test database server. On a production system, the INI file contains a value called "DatabaseServer that points to the Production server. When run from the production environment, the production server is accessed and when run from the test environment, the test server is accessed. This works nicely both for WTS terminal sessions and for batch jobs.

We've thought about more complicated schemes involving ActiveX script tasks (and we do use those for some things) but for Test/Prod, the Dynamic Properties task seems to be about all we need and it's simple.

Actually, there's one more component... We do some Execute Task functions and we also have some batch jobs that don't involve packages. These we mostly control with environment variables and command-line substitution (although we also use INI files for a few things).

"Joe Horton" <horj235 at lni dot wa dot gov> wrote

DTS Environment Control



[space-saving snip]