Migrating SQL Server 2000 DTS Packages Across Environments
-
Comments posted to this topic are about the item Migrating SQL Server 2000 DTS Packages Across Environments
-
Nice article. I've written something similar as a windows script file. One thing I noticed though was that the layout of the package is not preserved when using DMO to move them. If you've got a particularly complicated package therefore it can be practically unusable when it gets to the destination server.
Manually saving the package to another server DOES preserve it however.
-
Hello Owais Bashir Ahmed,
Thanks for this interesting Article!
I would like to add that you don't have to use the version ID when calling DTS Packages from within DTS Packages. Instead you can refer by name only.
Personally, I prefer the following approach regarding the deployment of DTS packages:
Each DTS Project requires a custom deployment package which has the following characteristics:
- Adjust Connection details like Server Name / Database Name / Username / Password
- Adjust other details like LogServerName or LogFolder
- Adjust custom DTS settings like a RetryCount, disabling a certain step, or just any other global variable.
The main difference is that there is no global deployment package for all DTS projects, since packages are usually not very similar and need different customizations. And what you are trying to avoid when moving a package is that you miss out that one important setting somewhere in one of the packages.
I also prefer to have a UI for the DBAs where they can adjust all relevant settings to simplify the installation for them.
Best Regards,
Chris Büttner
-
Nice article.
Myself, I prefer developing the package to allow easy migration, rather than designing a system to do the migration.
In DTS terms, I usually use a database table that holds all the global variables. Then a short ActiveXScript coupled with a Dynamic Properties task will allow super easy migration between environments. Having the global variables in a table makes it easy to adjust things like connection properties and file locations without going anywhere near the DTS Designer.
At a basic level, this approach is the same as the Package Configurations available in SQL Server 2005 Integration Services.
--------------------
Colt 45 - the original point and click interface
-
Why don't folks use structured storage files more, rather than storing packages in SQL Server? Putting packages in structured storage files makes moving packages around much easier (just a simple file copy) and it makes it possible to "version control" the packages too, if you want to do that. Editing the packages is a bit of a hassle (opening them to edit them, that is), but otherwise it seems to make it much easier to deal with.
We've got several packages for a couple of different systems that I've moved to structured storage files. And, after modifying the packages to allow them to be dynamically configured (via INI files), we've got one set of packages that can be used in three separate environments (development, staging, production). Structured storage files in combination with dynamic configuration solves many problems with deployment (except where Analysis Services is concerned, but that's a separate problem).
-
Storing packages in SQL Server, or as structured storage files is not a big issue. An advantage for storing in SQL Server is that you can transfer packages between servers with a simple insert query.
The main thing is to structure/develop the package so it is configured dynamically. Without the dynamic configuration you're in for many dreary hours editing packages every time they a moved 😉
--------------------
Colt 45 - the original point and click interface -
dmbaker (10/9/2007)
Why don't folks use structured storage files more, rather than storing packages in SQL Server?Well one thing was already mentioned: You loose your package layout. While this is no issue with simple packages, this is really a hassle, especially when you want to keep comments in your layout.
Putting packages in structured storage files makes moving packages around much easier (just a simple file copy) and it makes it possible to "version control" the packages too, if you want to do that.
Sometimes it is desireable to have a package in SQL Server. The nicest I can think of now is the way you open package logs. Only if you stored the package in SQL Server you can quickly open the logs by right clicking the Package in the repository.
We've got several packages for a couple of different systems that I've moved to structured storage files. And, after modifying the packages to allow them to be dynamically configured (via INI files), we've got one set of packages that can be used in three separate environments (development, staging, production).
Unfortunately there is an issue with INI files as soon as you reach a certain amount of settings in the file. This is why we stopped using the INI file.
Best Regards,
Chris Büttner
-
Not sure what you mean by "lose your package layout"...I don't think I've lost any layout in the packages I've migrated to structured sotrage files and I'm using embedded packages in a number of packages. Can you expand more on that?
I agree that it's convenient to look at the logs right in SQL Server, but you can store those logs in files too (dynamically configuring the package to write the log file to a particular location). That can make it "easier" for support folks as they don't have to connect to the server to look at the logs -- although with very large log files that can become quite difficult or impossible. But then, you're not cluttering up your SQL Server with all that data either.
Limits to INI file size can certainly limits its usefulness, but it's not the only method for storing configuration data, as Phil mentioned you can use the database as well, or some other method uf you want.
I agree with Phil, I think you get the most bang for your buck with dynamic configuration, once you do that then the actual physical location of the package becomes moot, migration becomes a matter of moving your package (if you need to at all) and changing your configuration data.
-
dmbaker (10/9/2007)
Not sure what you mean by "lose your package layout"...I don't think I've lost any layout in the packages I've migrated to structured sotrage files and I'm using embedded packages in a number of packages. Can you expand more on that?Hm, thanks for that hint. Its not storing the package as structured storage file which makes you loose the layout. It happens only when you load and save a package using the DTS Object model.
http://www.sqldts.com/204.aspx
I agree that it's convenient to look at the logs right in SQL Server, but you can store those logs in files too (dynamically configuring the package to write the log file to a particular location). That can make it "easier" for support folks as they don't have to connect to the server to look at the logs -- although with very large log files that can become quite difficult or impossible. But then, you're not cluttering up your SQL Server with all that data either.
Well as a developer I have my issues with that. Because you will probably want to log to local disk. And thats where a developer has usually no access to. Makes support difficult. Btw, you can dynamically establish the name of the log file at run-time and add a timestamp for example. (Unfortunately this doesnt work with logging to SQL Server)
Best Regards,
Chris Büttner
-
Well as a developer I have my issues with that. Because you will probably want to log to local disk. And thats where a developer has usually no access to. Makes support difficult. Btw, you can dynamically establish the name of the log file at run-time and add a timestamp for example. (Unfortunately this doesnt work with logging to SQL Server)
Yep, that can be a problem, but our support folks have the access they need (or we store the log files on a network share so I can see them as needed). And to manage the size of the log files I do exactly what you said, dynamically configure the log file name to include a timestamp, per run of the package. Trades one huge log file for a bunch of small(er) ones, but at least you can open and read the log file. 🙂 Bit of a hassle to set up the first time, but once you've done it you can reuse it wherever you need.
-
How do I download import.zip and extract.zip? I need to see the .bat files, not just the VB6 code files.
Thanks,
--Jeff
-
I like the dynamic configuration idea. Would you please post more details/codes? Thanks.
-
Chung, I started doing a series of articles for DTS on my blog (http://philcart.blogspot.com/search/label/DTS), but I shelved it after looking at the Package Configurations in SQL Server 2005 Integration Services.
These articles have some info on Package Configurations in Integration Services,
http://vyaskn.tripod.com/sql_server_2005_making_ssis_packages_portable.htm
--------------------
Colt 45 - the original point and click interface
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply