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 ««12

Portable DTS Packages Expand / Collapse
Author
Message
Posted Tuesday, July 26, 2005 10:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 7, 2009 12:55 PM
Points: 34, Visits: 3
Paul, I wish you were my DBA.  The VBScripts are required here because our DBA's refuse to touch a package or ini files once it's tested and sign-off.  I can say I like the removal of risk on their part, but it is extra overhead for me.


Post #204529
Posted Tuesday, August 9, 2005 11:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, December 14, 2014 10:57 PM
Points: 3, Visits: 102

Instead of using a .ini file to hold the server name, I use a .udl file to define the server connection then pass the path to the .udl file as a parameter when running the DTS package. That way I don't need to mess around with environment variables, something that I may not have access to do on the production server. If you use SQL Server security, the user name & password are also stored in the .udl file so you don't need to worry about them in DTS.

Also, in an article like this you should have mentioned that table names in Data Pump tasks are recorded as [database].[owner].[tablename]. If you want those tasks to be database-independent you need to use a Dynamic Properties task to change them to [owner].[tablename].

Don




Post #208833
Posted Tuesday, July 25, 2006 9:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 22, 2010 2:50 PM
Points: 41, Visits: 8
We had problems with .ini file.  It wouldn't show all parameters in the .ini file, only top 15.  Does anyone else have the same problem?  We ended up to put parameters in a config DB.  We also use UDL file stored on the local machine, so it can be hidden from the dev people to see the logins in production.
Post #297113
Posted Tuesday, July 25, 2006 9:43 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 12:14 PM
Points: 148, Visits: 655

Richard writes:

We had problems with .ini file.  It wouldn't show all parameters in the .ini file, only top 15.

 

That's good to know.  We have a VB script that uses an INI file that is assumed to be the same name as the package.  For deployment, we set only a gINIpath variable (UNC path) to tell it where to find the INI.  All settings are then in the INI, with no parameters.  Typical variables at the moment are source/target UDL path, production source, production server, test/prod folder paths, start/end date.  The packages all determine whether the source UDL connection is "production" and will export to production folders if it is.  We store the package and INI in VSS as it should look in production so we don't have to change anything for a production deployment.  It works pretty well.  Dynamic properties make me

We don't have more than 15 parameters in the INI, but I'll bring it up as a potential risk.

One would do well to realize that DTS doesn't need to "run on a server".  I'll be making a push soon to store them as .DTS and run them from a production folder path.  That comes right after obliterating our dependency on our Exchange server. 




Post #297121
Posted Tuesday, October 10, 2006 8:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 3, 2006 7:22 AM
Points: 3, Visits: 1

Well, I'm a bit late to the party here, but have to say "portable DTS packages" is pretty much of an oxymoron. 

I'm a former OO developer where we used DTS only to import/export data.  The packages were invoked from VB code and run-time properties set via the COM interface.

I'm now doing data warehousing using DTS only.  We're not allowed to touch the production server, so we have to develop the packages on a Dev box and turn them over to the DBA.  Until I came on board and suggested Dynamic Properties, he was almost rewriting the fool things.  While the DPs have helped a lot, I still find them a PITA to use, especially through the DTS designer interface.  That's why I typically save the package to a .BAS module then load to an Access DB where I add the Dynamic Properties task via a VB proc I wrote.  Also the only sure way to test the property mapping is to execute the task, then go look at your objects/properties.  It's extremely annoying to me that when you view the DP task, it doesn't show you which object/property the DP is mapped to (using .INI files for connection info).  Also the hard-coded path to the .INI file names means we have to map our drives exactly as on the production server so the package can find them.  That's why I'm looking at replacing the DP/.Ini file with an ActiveX script that reads an XML file from whatever the DTS file path is and populates global variables.

At least in SQL 2000, I find DTS is way too server and object-centric to be portable or scalable.  That's why in all of my past uses, the packages were kept dumb as a rock, no business rules at all were allowed to reside in them, just firehose data in and out, let the VB code and stored procedures do the brain work and use .UDL files for all DB connections.

 

Post #314375
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse