SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Programmatically Save DTS Packages to Files
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 05/24/2004 :  07:06:13  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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 Posts

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

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 06/15/2004 :  19:28:46  Show Profile  Visit nr's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1429 Posts

Posted - 06/16/2004 :  01:14:38  Show Profile  Reply with Quote
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.



Edited by - ValterBorges on 06/16/2004 01:15:27
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 06/16/2004 :  16:51:04  Show Profile  Visit nr's Homepage  Reply with Quote
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 - 01/16/2005 :  21:19:09  Show Profile  Reply with Quote
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 Posts

Posted - 01/20/2005 :  09:39:06  Show Profile  Reply with Quote
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

USA
665 Posts

Posted - 01/20/2005 :  10:47:35  Show Profile  Reply with Quote
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 - 01/20/2005 :  13:39:33  Show Profile  Reply with Quote
Xerxes,

This functionality will likely be available in SQL Server 2005.

regards
Go to Top of Page

Xerxes
Aged Yak Warrior

USA
665 Posts

Posted - 01/20/2005 :  14:07:32  Show Profile  Reply with Quote
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

9 Posts

Posted - 11/07/2007 :  12:11:30  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000