SQLServerCentral / Discuss Content Posted by Kevin Feit / Article Discussions / Article Discussions by Author / Portable DTS Packages / Latest Posts

RE: Portable DTS Packages

nigel.knowles — Mon, 03 Dec 2007 05:46:32 GMT

Very useful article.Initially I had problems with saving the dts package as the article quotes dtsrun /Fmydtspkg /Nmydtspkgbut /F is actually the full filename e.g c:\dtspackages\mypkg.dtsand /N is the name of the package e.g. mypkg I also initially had problems realising that 'structured storage' simply means a disk file and that is what we are refering to.Note that your structured storage holds a new version of the package each time it is saved so you need to delete the file before each save or dtsrun will tell you there are multiple packages to choose from

RE: Portable DTS Packages

Philippe Cand — Fri, 20 May 2005 14:06:00 GMT

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=99&messageid=184282

RE: Portable DTS Packages

Mark Tierney — Fri, 20 May 2005 11:27:00 GMT

Hi BankaR,

I added this code in an ActiveX script at the top of my package:

For i = 1 to oPackage.Tasks.Count ' Verify that this task is a transformation task by interrogating for default name If Left(oPackage.Tasks(i).Name, 19) = "DTSTask_DTSDataPump" Then PumpID = i set oTrans = oPackage.Tasks(PumpID) ' Get the Destination tablename and find the 2nd qualifier location oTask = oTrans.CustomTask.DestinationObjectName tst = InStr(1 ,oTask,"dbo") tst2 = tst - 2 ' Use calculated starting point to strip off the table creator and name ' from the server name tempname = Right(oTask,Len(oTask) - tst2) ' Apply stripped name back to destination name oTrans.CustomTask.DestinationObjectName = tempname set oTrans = nothing End If Next

I use this loop to also change other aspects of the Tasks (just add new code to loop) when I have many of them in a single package. It allows you to avoid opening each Task.

RE: Portable DTS Packages

Alex-217289 — Fri, 20 May 2005 08:41:00 GMT

Once again, the submission is SPOT ON. Good job and thanks for the very well written and easy to understand style!

RE: Portable DTS Packages

einman33 — Fri, 20 May 2005 08:20:00 GMT

Excellent, thanks. No more resetting of transformations, YES!!

RE: Portable DTS Packages

LoveSQL — Fri, 20 May 2005 07:42:00 GMT

Hey andyj93

How did you get rid of the three part naming in the tranformation?

I have the source from ODBC AS400 and destination is the SQL server. I am able to set the global varible for the connections but not the transformation. Any inmput is appreciated.

THanks.

RE: Portable DTS Packages

Peter Kryszak — Fri, 20 May 2005 07:35:00 GMT

This is exactly what we do where I work. For those that don't like the idea of running DTS packages from the command line, try scheduling a DTS package as a job. You'll find that the job itself uses DTSRun.exe, so there isn't much difference.We wrap a substantial but not complicated DOS command scripts around the DTSRun call too. We get a lot of flexibility and our staff who don't know SQL can make script adjustments and updates without much problem.

RE: Portable DTS Packages

Mark Tierney — Fri, 20 May 2005 06:03:00 GMT

Good Article. I wish there was something like it when I first started doing portable DTS work.

Chalk me up as another fan of UDL and INI files.

To add to andyj93's comments, you can write a failirly simple vbscript to chop out the 1st and 2nd parts of the three part name and stick it into an ActiveX script in your package (runs early). That way you do not need to do the editting.

RE: Portable DTS Packages

Phu Truong — Fri, 20 May 2005 05:53:00 GMT

Great article! Very easy to follow. I was looking for something like this several months ago and could not find it on the web. I end up writing a VB app to run it. Now I am going to change my DTS.Thanks

RE: Portable DTS Packages

Bruce Gilpin — Mon, 10 May 2004 09:37:00 GMT

This article came along at an opportune time as I was working on a DTS package that will be moved to production. My only problem was with the diagram that showed the dynamic properties box directly connected to a connection object. My version of DTS would not allow this. I got an "Invalid precedence" error message. I was able to make it work when I connected the task to the the first "Execute SQL" task. I belive this has the same affect as the steps outlined in the article.

RE: Portable DTS Packages

Undebtedly — Mon, 10 May 2004 09:19:00 GMT

Good article.

We have hundreds of databases distributed all over the world. Anyone tried using DTS to update the schema of distributed databases like these? Is dtsrun.exe distributable?

Steve

RE: Portable DTS Packages

dwebb — Mon, 10 May 2004 07:02:00 GMT

very informative

RE: Portable DTS Packages

tcruse — Mon, 12 May 2003 08:06:00 GMT

Good article, for PB users the pipeline object is an alternate approach, especially for the person that wants to dynamiclly get the table names based on some query, also DTS is not very forgiving of data conversion errors, a nice followon article about handling conversion errors and overflow/underflow during DTS copy from .csv file would be helpful

RE: Portable DTS Packages

naushada — Sun, 11 May 2003 02:37:00 GMT

Vey nice, I was really looking for something and was thinking that SQL server must be having this sort of functionality. Lack of time couldn't give time to explore the things.This technique will be definitely helpful to me in future....-:)-Naushad

RE: Portable DTS Packages

srini_kris — Thu, 08 May 2003 09:53:00 GMT

Great article as this is something i have a need for. Since i am copying tables from server/db A to server/db B, is there a way to dynamically select all tables that begin with the letter 't' for example.In other words, i want to copy all tables and the list of tables keeps getting increased because of constant development. Any ideas will be appreciated-sk

RE: Portable DTS Packages

Moshe Eshel — Thu, 08 May 2003 02:30:00 GMT

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

RE: Portable DTS Packages

dbamark — Wed, 08 Jan 2003 08:10:00 GMT

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

RE: Portable DTS Packages

Kevin Feit — Wed, 11 Dec 2002 06:55:00 GMT

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

RE: Portable DTS Packages

Antares686 — Wed, 11 Dec 2002 06:00:00 GMT

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.

RE: Portable DTS Packages

philcart — Mon, 09 Dec 2002 15:10:00 GMT

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

RE: Portable DTS Packages

Matthew Galbraith — Fri, 06 Dec 2002 12:21:00 GMT

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

RE: Portable DTS Packages

trey_johnson — Fri, 06 Dec 2002 08:46:00 GMT

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

RE: Portable DTS Packages

Klaas-Jan — Fri, 06 Dec 2002 02:50:00 GMT

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.

RE: Portable DTS Packages

don1941 — Thu, 05 Dec 2002 21:05:00 GMT

cool. let me go vote ...

RE: Portable DTS Packages

GoTroppo — Thu, 05 Dec 2002 16:23:00 GMT

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

RE: Portable DTS Packages

xiatprogressive — Thu, 05 Dec 2002 07:35:00 GMT

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.

RE: Portable DTS Packages

andyj93 — Thu, 05 Dec 2002 07:24:00 GMT

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

RE: Portable DTS Packages

xiatprogressive — Thu, 05 Dec 2002 06:57:00 GMT

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.

RE: Portable DTS Packages

andyj93 — Thu, 05 Dec 2002 04:48:00 GMT

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 JonesEdited by - andyj93 on 12/05/2002 04:49:44 AM

Portable DTS Packages

Kevin Feit — Sat, 30 Nov 2002 00:00:00 GMT

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