Portable DTS Packages

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Kevin Feit » Portable DTS Packages

30 posts, Page 2 of 3

««1 2 3 »»

Portable DTS Packages

Rate Topic

Display Mode

Topic Options

Author

Message

philcart

Posted Monday, December 09, 2002 3:10 PM

SSCrazy

Group: General Forum Members
Last Login: Friday, May 17, 2013 5:53 AM
Points: 2,692, Visits: 1,074

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

Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club

Post #48177

Antares686

Posted Wednesday, December 11, 2002 6:00 AM

SSCrazy Eights

Group: Moderators
Last Login: Tuesday, April 09, 2013 12:53 PM
Points: 8,357, Visits: 684

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.

Post #48178

Kevin Feit

Posted Wednesday, December 11, 2002 6:55 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, June 12, 2012 9:36 PM
Points: 4, Visits: 13

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

Post #48179

dbamark

Posted Wednesday, January 08, 2003 8:10 AM

Say Hey Kid

Group: General Forum Members
Last Login: Tuesday, June 19, 2012 6:59 AM
Points: 692, Visits: 53

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

Post #48180

Moshe Eshel

Posted Thursday, May 08, 2003 2:30 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 08, 2012 8:13 AM
Points: 38, Visits: 17

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

Post #48181

srini_kris

Posted Thursday, May 08, 2003 9:53 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, August 04, 2006 8:32 AM
Points: 84, Visits: 1

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

Post #48182

naushada

Posted Sunday, May 11, 2003 2:37 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, June 04, 2004 1:33 PM
Points: 1, Visits: 1

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

Post #48183

tcruse

Posted Monday, May 12, 2003 8:06 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 12, 2012 11:41 AM
Points: 162, Visits: 11

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

Post #48184

dwebb

Posted Monday, May 10, 2004 7:02 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, May 13, 2013 12:00 PM
Points: 39, Visits: 88

very informative

Post #115123

Undebtedly

Posted Monday, May 10, 2004 9:19 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 20, 2009 3:33 PM
Points: 124, Visits: 13

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

Steve Miller

Post #115179

« Prev Topic | Next Topic »

30 posts, Page 2 of 3

««1 2 3 »»

Permissions