SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Portable DTS Packages


Portable DTS Packages

Author
Message
SueStill
SueStill
SSC-Enthusiastic
SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)

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



donr
donr
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 113

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





Richard Rao-159381
Richard Rao-159381
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
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.
Larry Aue
Larry Aue
Old Hand
Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)

Group: General Forum Members
Points: 303 Visits: 668

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.





James Carmichael
James Carmichael
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

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


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