Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Site Related Forums
 Article Discussion
 Article: Programmatically Save DTS Packages to Files

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-05-24 : 07:06:13
One of the things that has always bugged me about DTS is how difficult it was to transfer the DTS packages from SQL Server to structured storage (aka a file on the disk). In Yukon, DTS packages are always stored as files but in SQL Server 2000 they are stored in SQL Server by default. It's possible to use the DTSRUN command to automatically save a DTS package in a file. So wrote a little script to generate the statements to save every DTS package as a file.

Article Link.

Paul Ho
Starting Member

1 Post

Posted - 2004-06-15 : 13:49:17
how do I open the dts file saved for edit ?
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-15 : 19:28:46
Or you can do it using ole SPs
http://www.nigelrivett.net/SaveAllDTSPackagesToFiles.html

>> how do I open the dts file saved for edit ?
From the dts design - load from structured storage file.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2004-06-16 : 01:14:38
Get DTSBackup a great free tool which can be found at www.sqldts.com
Instance to Instance, File to Instance, and Instance to File migration of DTS packages. Now if they could only come up with a change management add on to dts it would be nice. I believe that and a lot more ie(Branching, Logical(and/or/xor), Versioning, Better source system support including change data capture and a bigger variety of transformations ie: pivot is now part of Yukon.

Until then if you can afford it I just got a demo of BO Data Integrator and it looks like a great product for 1/10th of informatica.


Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-16 : 16:51:04
You can save as a VB file and use that for release control.
I have a procedure that scripts the properties of packages. You can't recreate from it but it's good for searching and detecting changes.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

ashvinis
Starting Member

2 Posts

Posted - 2005-01-16 : 21:19:09
Minor correction: note that in Yukon (SQL Server 2005), Packages are not limited to the file system. They can be stored in SQL Server as well.
Go to Top of Page

Sideout72
Starting Member

1 Post

Posted - 2005-01-20 : 09:39:06
Great post. DTS is powerful, but dealing with DTS packages are a pain. Moving packages from one server to another usually require modifying the DTS package once it gets to the new server, etc. Not to mention having a lot of DTS packages and needing to go into each one, save them to a .dts file, and then load each one up. This is so much simpler. Thanks!
Go to Top of Page

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-01-20 : 10:47:35
I just wish there was a way to store DTSs in individual directories within the individual table directories.

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC
Go to Top of Page

ashvinis
Starting Member

2 Posts

Posted - 2005-01-20 : 13:39:33
Xerxes,

This functionality will likely be available in SQL Server 2005.

regards
Go to Top of Page

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-01-20 : 14:07:32
Ashvinis,

I sure hope you're right. My DTS/Local Packages directory is getting unruly....



~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC
Go to Top of Page

kissa49
Starting Member

10 Posts

Posted - 2007-11-07 : 12:11:30
I am having a really difficult time with something: I transfer data from a table to a file, that's no problem, but then that file needs to be saved to a newly created folder in a specific location. So my trouble is creating a new folder each time the package is ran and that file is created. Plus the name of the folder has to be increased by 1 each time as well. An example of that would be fldrName1, fldrName2, etc.
I've tried using ActiveX and still am so lost on that.
If anyone knows how to do this, please resspond.
Thanks a lot!
Go to Top of Page
   

- Advertisement -