Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Portable DTS Packages Expand / Collapse
Author
Message
Posted Saturday, November 30, 2002 12:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 5:49 AM
Points: 4, Visits: 16
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/kfeit/portabledtspackages.asp


Post #8511
Posted Thursday, December 5, 2002 4:48 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, June 27, 2014 1:32 PM
Points: 1,104, Visits: 332
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].[table name] 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



Regards,
Andy Jones
Post #48168
Posted Thursday, December 5, 2002 6:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 5, 2002 12:00 AM
Points: 2, Visits: 1
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.


Post #48169
Posted Thursday, December 5, 2002 7:24 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, June 27, 2014 1:32 PM
Points: 1,104, Visits: 332
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



Regards,
Andy Jones
Post #48170
Posted Thursday, December 5, 2002 7:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 5, 2002 12:00 AM
Points: 2, Visits: 1
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.



Post #48171
Posted Thursday, December 5, 2002 4:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, December 20, 2009 7:56 PM
Points: 9, Visits: 25

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




Post #48172
Posted Thursday, December 5, 2002 9:05 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, October 17, 2003 12:00 AM
Points: 299, Visits: 1
cool. let me go vote ...




Post #48173
Posted Friday, December 6, 2002 2:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 10, 2002 12:00 AM
Points: 135, Visits: 1
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.




Post #48174
Posted Friday, December 6, 2002 8:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 6, 2007 11:16 AM
Points: 34, Visits: 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
Post #48175
Posted Friday, December 6, 2002 12:21 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 14, 2008 12:28 PM
Points: 49, Visits: 4
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



Post #48176
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse