Portable DTS Packages

Question

Portable DTS Packages

Kevin Feit

SSC Enthusiast

Points: 198
More actions
November 30, 2002 at 12:00 am

#105925

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/kfeit/portabledtspackages.asp

Viewing 15 posts - 1 through 15 (of 29 total)

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

Andy Jones, DBA Hall of Fame Points: 3641 More actions · Answer 1

Nice article - well explained. I use this method to dynamically assign server / database name for a database connection when transferring data from a text file to a database table. One point which initially caused me an error - although I changed the database connection properties via a dynamic properties task the data was not being transferred to the correct database. This was because, by default, the 'transform data task' assigns the destination with a three part syntax [Database name].[owner].

so the [Database name] was always the value from the saved package. I got around this by deleting the first two parts [Database name] and [Owner] then it works. One further point, you can't manually edit the destination to remove the first two parts, I assigned it to be a constant within the dynamic properties task i.e. [Table name] then everything works dynamically as required.

Regards,

Andy Jones

Edited by - andyj93 on 12/05/2002 04:49:44 AM

.

xiatprogressive SSC Rookie Points: 42 More actions · Answer 2

In general, it is a good article. But if you have many dynamic properties to set, and would like to keep a record of all history dynamic properties values, you might want to use a database to store those values. Running DTS packages from command line is not convenient, and not my choice.

Andy Jones, DBA Hall of Fame Points: 3641 More actions · Answer 3

What is the alternative if you are calling the package from a stored procedure?

I use this method becuase you can pass in the variables @@servername and db_name() to the package (via the command line and xp_cmdshell), then you could have 100 different databases all with different names on different servers using the same generic package.

Regards,

Andy Jones

.

xiatprogressive SSC Rookie Points: 42 More actions · Answer 4

I still use dynamic properties, but not running DTS from Command Line. The command line approach is a good way of migrating DTS packages, but not the best one.

I cannot give you all the detail of implementation, as it is banned at our company. All I can tell you is that we used to adopt this approach (command line), but we optioned to use VBScript and SQL Tables, which can easily control dynamic properties settings. No stored procedure is needed.

GoTroppo Valued Member Points: 59 More actions · Answer 5

We've been experimenting along a similar line recently. However, rather than using command line calls, we've used the INI file capabilities of Dynamic Properties and Global Variables. That way, we simple schedule and leave it to run (after setting up the INI file of course).

don1941 SSCarpal Tunnel Points: 4183 More actions · Answer 6

don1941

SSCarpal Tunnel

Points: 4183

December 5, 2002 at 9:05 pm

#443407

cool. let me go vote ...

Klaas-Jan SSCrazy Points: 2297 More actions · Answer 7

nice article , i wish i had dynamic properties in sql 7. You can also start a dts package from a job , you can use the same technique of passing variables to a package.

trey_johnson Mr or Mrs. 500 Points: 568 More actions · Answer 8

Decent article....

Why not just use a UDL file for connection information? A UDL file is a better candidate because it can be RE-USED across multiple packages.

Good of you to put this out!

Trey Johnson

Trey Johnson | Chief Business Intelligence Architect | Cizer Software (www.cizer.com)

Who? - Cizer - http://www.cizer.com/about.htm - Blog - http://www.sqlserverbi.com/
What? - Products enhancing Microsoft Business Intelligence - http://www.cizer.com/products.htm
Wow! - Empower your Developers.... NEW Drop In Reporting - http://www.cizer.com/cnr-drop-in-reporting.htm
How? - BI Training - http://www.cizer.com/training.htm - Cizer Solutions - http://www.cizer.com/solutions.htm

Matthew Galbraith SSC Eights! Points: 995 More actions · Answer 9

Good article. Dynamic DTS is one of the areas I want to become more familiar with.

GoTroppo noted the use of INI files; I wanted to throw in my copper bits and say this is the method I prefer as well. I haven't used this to migrate DTS packages, but by using an INI file, you have a very simple way to configure a DTS package either manually, or programmatically using vbscript, vb, or whatever. In scenarios where you have to pull data from a given table into segmented output files, it saves a huge amount of time. Then when you have to redo one or two of the files, it's pretty easy to change the .INI file which avoids having to change any code in a vb project or having to spend extra time coding the vb/vbscript/etc... in a way to make it more flexible.

The case where I used INI files, I passed a list file of parameters to a vb program, and it built the require sql statement and INI file for each set. The DTS package used global variables set off of the INI file and, for the SQL statement, a text file. The vb program also called the DTS package for each set of parameters, which included the full path to the file; this let me test on my workstation but run from the server(using UNC paths helps with this part too).

Matthew Galbraith

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

Good article. It's a pity Microsoft didn't think through the real-life development cycle for DTS packages.

FWIW, here's what I do with my packages. I pass the Server, database, userid, password, security mode (Windows or SQL) and a logging flag (log execution or not) in as global variables. The first step of my packages is a Dynamic Properties task which sets all the connection properties.

In the case of using a datapump, I also store the source and destination in a table and assign them dynamically at runtime. Generally the source is an SQL statement and the destination is a three part table name.

Because everything is dynamic the packages are easily moved through development, QA, Functional Test, Unit Test and Production.

Thanks

Phill Carter

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

Antares686 SSC Guru Points: 125444 More actions · Answer 11

I haven't had a need for this myself so had not explored. But seeing it here gives me an idea what to do if I ever do need. Good article.

Kevin Feit SSC Enthusiast Points: 198 More actions · Answer 12

quote:
Regarding using a UDL instead...
Thank you for your thoughts.
Using a UDL is certainly an alternative if all you need to control is the connection string. However, the technique described can be used to control other variables, such as the location of input or output files.
By using the batch file, it can also be re-used across multiple packages.
Arguing which is "better" is like arguing whether a screwdriver or hammer is better. The goal was to present a tool - if it meets your needs, great; if not, then you should use something else.
Thanks,
Kevin Feit

Edited by - feitke on 12/11/2002 06:58:11 AM

dbamark SSCommitted Points: 1694 More actions · Answer 13

Good article. Gives us all a way to accomplish a task that will work best in some situations!

Moshe Eshel SSC Enthusiast Points: 154 More actions · Answer 14

Great article, personally I use DTS primarily through VB.

I design the package in EM and save as VB then edit it in my editor and assign Variables to the values I want to do progamatically, it works great and allows me to use DTS for high quality and speed issues in the tools I create.

however, the concept of using Global variables is an excellent Idea, and I will start designing my new DTS packages with that in mind. (it is so much easier than browse down the code and look for all the relevant places...