Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Portable DTS Packages


Portable DTS Packages

Author
Message
Kevin Feit
Kevin Feit
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 18
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/kfeit/portabledtspackages.asp



Andy Jones, DBA
Andy Jones, DBA
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1178 Visits: 535
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

.
xiatprogressive
xiatprogressive
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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.



Andy Jones, DBA
Andy Jones, DBA
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1178 Visits: 535
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
xiatprogressive
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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.



GoTroppo
GoTroppo
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
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).



don1941
don1941
SSC Veteran
SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)

Group: General Forum Members
Points: 299 Visits: 1
cool. let me go vote ...



Klaas-Jan
Klaas-Jan
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 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.



trey_johnson
trey_johnson
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
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
Matthew Galbraith
Matthew Galbraith
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
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



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