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 Monday, December 9, 2002 3:10 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 16, 2014 6:41 PM
Points: 2,693, Visits: 1,219
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
Posted Wednesday, December 11, 2002 6:00 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Friday, November 14, 2014 10:06 AM
Points: 8,370, Visits: 745
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
Posted Wednesday, December 11, 2002 6:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 5:49 AM
Points: 4, Visits: 16
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
Posted Wednesday, January 8, 2003 8:10 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay 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
Posted Thursday, May 8, 2003 2:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 23, 2014 9:40 PM
Points: 38, Visits: 18
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
Posted Thursday, May 8, 2003 9:53 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, August 4, 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
Posted Sunday, May 11, 2003 2:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 4, 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
Posted Monday, May 12, 2003 8:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Monday, May 10, 2004 7:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 10:41 AM
Points: 39, Visits: 100

very informative




Post #115123
Posted Monday, May 10, 2004 9:19 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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 »

Add to briefcase ««123»»

Permissions Expand / Collapse