Portable DTS Packages

  • Kevin Feit

    SSC Enthusiast

    Points: 198

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

  • Andy Jones, DBA

    Hall of Fame

    Points: 3641

    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

    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

    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

    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

    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

    cool. let me go vote ...

  • Klaas-Jan

    SSCrazy

    Points: 2267

    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: 558

    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

    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: 47713

    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

    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

    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: 1614

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

  • Moshe Eshel

    SSC Enthusiast

    Points: 124

    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...

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

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