SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Migrating SQL Server 2000 DTS Packages Across Environments


Migrating SQL Server 2000 DTS Packages Across Environments

Author
Message
Owais
Owais
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 6
Comments posted to this topic are about the item Migrating SQL Server 2000 DTS Packages Across Environments
DanKennedy
DanKennedy
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2392 Visits: 389
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.



Dan
www.firstcs.co.uk
Christian Buettner-167247
Christian Buettner-167247
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7181 Visits: 3889
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
philcart
philcart
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16529 Visits: 1441
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.

Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
dmbaker
dmbaker
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2118 Visits: 3644
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).



philcart
philcart
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16529 Visits: 1441
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 Wink

Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
Christian Buettner-167247
Christian Buettner-167247
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7181 Visits: 3889
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
dmbaker
dmbaker
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2118 Visits: 3644
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.



Christian Buettner-167247
Christian Buettner-167247
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7181 Visits: 3889
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
dmbaker
dmbaker
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2118 Visits: 3644


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. Smile Bit of a hassle to set up the first time, but once you've done it you can reuse it wherever you need.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search