save all dts to .Bas

Question

save all dts to .Bas

partner55164324

SSC Enthusiast

Points: 162
More actions
April 8, 2008 at 2:43 am

#71135

I need some process or procedure that will allow me to save all dts to .Bas

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Bryansky SSCommitted Points: 1992 More actions · Answer 1

open the DTS package in DTS Designer, Click Package from the menu, then Save As, Choose Visual Basic File from the Location drop down menu, then browse the folder where you will save the file, click OK.

:-):cool:

partner55164324 SSC Enthusiast Points: 162 More actions · Answer 2

Ok, the problem is that I have to do for some 1000 dts

Bryansky SSCommitted Points: 1992 More actions · Answer 3

OK... you can check this one if can help you...

http://www.sqlservercentral.com/Forums/Topic197141-8-1.aspx#bm197629

I am not sure if there are tools available for your purpose. Last time I checked, the DTSBackup 2000 from http://www.sqldts.com don't have the capability. But you can check with the tool if there are new version that supports that function.

:-):cool:

EdVassie SSC Guru Points: 60472 More actions · Answer 4

There is a sp in SqlServerFindBuild that does a bulk export of DTS packages. The sp outputs to structured storage files, but it is easy to tweak the output specification to provide vbs output.

Alternatively google may find you a routine that already gives vbs output.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

curbina SSC-Addicted Points: 411 More actions · Answer 5

I was looking for something like this a few years ago. What I really wanted to do was to parse the result not necessarily change it and put it back (which I think you can do). At any rate, I stopped looking when I found Nigel Rivett's script which allows an encredible amount of parsing of most of the DTS object model. I've changed it so that I can extract pertinent relationships and dependencies from the object model into SQL Server tables.

http://www.nigelrivett.net/ScriptDTSProperties.html

curbina SSC-Addicted Points: 411 More actions · Answer 6

I used this script 2 years ago and it gave me a little trouble, but you can work with it. I apologize that I did not save the author's name, but if you Google some of the text in this script, you may be able to find the original post.

Automating the saving of DTS packages to .BAS format

DECLARE @TARGETDIR varchar(1000)

SET @TARGETDIR = 'd:\backup\sqlbackup\dtspackage\'

SELECT distinct

'exec master..xp_cmdshell '+'DTSRUN.EXE /S '

+ CONVERT(varchar(200), SERVERPROPERTY('servername'))

+ ' /E '

+ ' /N '

+ '"' + name + '"'

+ ' /F '

+ '"' + @TARGETDIR + name + '.bas"'

+ ' /!X'

FROM msdb.dbo.sysdtspackages P

After I got the list from the above statement, I created a job like this:

exec master..xp_cmdshell 'DTSRUN.EXE /S DMISQL05 /E /N "Acor_LinkShare_Inventory" /F "\d:\\sql05backup\dtspackage\Acor_LinkShare_Inventory.dts" /!X'