DTS packages

Question

Post reply

DTS packages

RGP

SSCarpal Tunnel

Points: 4381
More actions
July 28, 2008 at 7:52 am

#190125

Hi, please .. a script that could be used to save all the dts packages of a server??
anyone has a script for that??
thanks

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

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

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

I found this in the script section, but I haven't used it.

http://www.sqlservercentral.com/scripts/Miscellaneous/31613/

Greg

RGP SSCarpal Tunnel Points: 4381 More actions · Answer 2

Greg Charles (7/28/2008)
I found this in the script section, but I haven't used it.
http://www.sqlservercentral.com/scripts/Miscellaneous/31613/

Thanks Greg..but this didnt work 🙁

2 Tim 3:16 SSCarpal Tunnel Points: 4541 More actions · Answer 3

I've used a free download called DTS Backup 2000. You can get a copy here; http://www.sqldts.com/242.aspx

Tim White

Timothy J Hartford SSCarpal Tunnel Points: 4214 More actions · Answer 4

Another option is to script the packages into import or export commands and execute the commands (you could use output to save to a text, or run an ssis package that exports the commands and then processes them). That would give you the ability to control your ssis packages from within an SSIS package, without any third party add-ons.

;WITH SSISFolders AS

(

SELECT folderid, parentfolderid, foldername, CAST(foldername AS VARCHAR(MAX)) AS [path], 1 AS depth

FROM msdb.dbo.sysdtspackagefolders90

WHERE parentfolderid is null

UNION ALL

SELECT b.folderid, b.parentfolderid, b.foldername, CAST(a.path+'\'+b.foldername AS VARCHAR(MAX)) , a.depth + 1

FROM SSISFolders a

JOIN msdb.dbo.sysdtspackagefolders90 b

ON a.folderid = b.parentfolderid

)

SELECT

'dtutil /SQL ' + ISNULL(b.path, '') + '\' + a.name + ' /COPY FILE;c:\LocalPackageFolder\' + a.name + '.dtsx' AS SqlToCentralFolder

, 'dtutil /SQL ' + ISNULL(b.path, '') + '\' + a.name + ' /COPY FILE;c:\LocalPackageFolder' + ISNULL('\' + NULLIF(b.path,''), '') + '\' + a.name + '.dtsx' AS SqlToPathedFolder

, 'dtutil /FILE c:\LocalPackageFolder\' + a.name + '.dtsx /COPY SQL;' + ISNULL(b.path, '') + '\' + a.name AS CentralFolderToSQL

, 'dtutil /FILE c:\LocalPackageFolder' + ISNULL('\' + NULLIF(b.path,''), '') + '\' + a.name + '.dtsx /COPY /SQL;' + ISNULL(b.path, '') + '\' + a.name AS PathedFolderToSQL

FROM msdb.dbo.sysdtspackages90 a

LEFT JOIN SSISFolders b

ON a.folderid = b.folderid

Timothy J Hartford SSCarpal Tunnel Points: 4214 More actions · Answer 5

Sorry, missed the "DTS" part... this is a much simpler query:

SELECT 'dtutil /DTS ' + [name] + ' /COPY FILE;c:\LocalDTSPackageFolder\' + [name] + '.dtsx' AS SqlToCentralFolder

, 'dtutil /FILE c:\LocalDTSPackageFolder\' + [name] + '.dtsx /COPY DTS;' + [name] AS CentralFolderToSQL

FROM msdb.dbo.sysdtspackages

Note: I haven't tested the exact command line output by these, so they may require tweeking.