Automating “Save DTS package”

MAK examines how to save all of the DTS packages from
SQL Server to a structured storage file automatically by taking advantage of Microsoft’s
stored procedure, "DTSRUN.exe." As you know, DTS can have many
versions. This procedure will dump every version of the same DTS package as
different file.

Step 1

Create the
folder "DTS" on the SQL Server box’s hard drive.

Eg:

MKDIR d:\DTS

Step 2

Execute
the source code below in query analyzer. [refer Fig:1.1] This will create the
procedure "SP__SaveDTS.sql"

Source Code


use master
go
if exists (select * from dbo.sysobjects
where id = object_id(N’[dbo].[sp__SaveDTS]’)
and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
drop procedure [dbo].[sp__SaveDTS]
GO
set quoted_identifier off
go
CREATE proc sp__SaveDTS
@DTSname varchar(256) = ”,
@Applicationpath varchar(700) =”,
@destinationpath varchar(700) =’D:\DTS\’,
@switches varchar(200) = ‘ -E -!X ‘
as
–Created by:MAK
–Date: Aug 29, 2004
–Objective: Save all or given DTS package to a folder
set quoted_identifier off
set nocount on
set concat_null_yields_null off
declare @count int
declare @folderexist int
declare @maxcount int
declare @query varchar(1000)
declare @date varchar(10)
declare @versionid varchar(40)
declare @createdate varchar(25)

set @date = convert(varchar(10),getdate(),112)
set @count =1
Print ‘Saving DTS packages – Started’
print getdate()
set @Applicationpath = @Applicationpath +’DTSRUN.exe’
create table #DTSTABLE(id int identity(1,1), DTSname varchar(256),
versionid varchar(40), createdate varchar(25))
if @dtsname = ”
begin
insert into #DTSTABLE (dtsname,versionid,createdate)
select name,versionid,replace(replace(convert(varchar(25),createdate,109),’:’,’ ‘),’ ‘,’_’) from
msdb..sysdtspackages
–drop table #DTSTABLE
end
else
begin
insert into #DTSTABLE (dtsname,versionid,createdate)
select name,versionid,replace(replace(convert(varchar(25),createdate,109),’:’,’ ‘),’ ‘,’_’) from
msdb..sysdtspackages where name =@DTSname
end

if (select count(*) from #dTStable) = 0
begin
set @date = convert(varchar(100), getdate(),109)
Print ‘Error: No valid DTS package found for saving’
end
else
begin
set @destinationpath = @destinationpath +@date
create table #files (Files int, Folder int, parent int)
insert #files exec master.dbo.xp_fileexist @destinationpath
select @folderexist = Folder from #files
if @folderexist <>1
begin
set @query = ‘MKDIR “‘+@destinationpath+'”‘
print @query
exec master..xp_cmdshell @query
set @destinationpath = @destinationpath
end
else
begin
print ‘Information:’+ @destinationpath + ‘ already exist. Skipping Folder Creation’
end
set @maxcount = (select max(id) from #dTStable)
While @count <= @maxcount
begin
select @dtsname =dtsname,@versionid=versionid ,@createdate =createdate from #DTSTABLE where id = @count
set @query = ”+@applicationpath +”+ ‘
-S”‘+@@servername+ ‘” -N”‘+@dtsname+'” -V”‘+@versionid +'”
-F”‘+@destinationpath++’\’+@dtsname+’_’+@createdate+’.dts”‘+ @switches
set @query = @query
set @query = “exec master..xp_cmdshell ‘” + @query + “‘”
print @query
exec(@query)
— exec master..xp_cmdshell @query

if @@error <> 0
begin
Print ‘Error’
end

set @count = @count+1
end

end
print getdate()
Print ‘Save DTS packages – Completed’

Download
the above stored procedure here.



[Fig 1.1]

Step 3

You can
execute the above-created procedure either by passing the right parameters or
by using the default parameters as shown below.

Exec sp__SaveDTS

This statement will save all of the databases on the
current server and dump it to d:\DTS\yyyymmdd folder where yyyymmdd is today’s
date. This uses default parameters.

or

Exec sp__SaveDTS 'DTS1', '','D:\DTS\'

This statement will save all versions of the DTS package DTS1
on the current server and dump it to d:\DTS\yyyymmdd folder where yyyymmdd is
today’s date.

Note

  • Parameter 1 is
    the DTS name. If you leave it blank, it saves all of the DTS packages. Eg: or ‘DTSPackagename’.
    Default value is

  • Parameter 2 is
    the path information of DTSRUN.exe Default value is ”

  • Parameter 3 is
    the path information of the destination folder where you would like the DTS
    Structure storage file do be dumped. Default value is ‘D:\DTS\’

  • Parameter 4 is
    the various optional switches used in scptxfr.exe Default value is ‘ -E -!X’

All of the above Execute statements produce similar
messages on the output window as shown below. All the above Execute statements
will create subfolders under d:\DTS\ as shown in the figure FIG 1.2

Results


Saving DTS packages – Started
Aug 29 2004 1:07PM
Information:D:\DTS\20040829 already exist. Skipping Folder Creation
exec master..xp_cmdshell ‘DTSRUN.exe -S”SQL” -N”MyImportDTS1″
-V”39A3296A-C534-4E7C-B8CA-D27DB2CAAB3B”
-F”D:\DTS\20040829\MyImportDTS1_Aug_28_2004__8_29_37_000A.dts” -E -!X ‘
output
————————————————————————–
DTSRun: Loading…
DTSRun: Executing…
NULL

exec master..xp_cmdshell ‘DTSRUN.exe -S”SQL” -N”dts1″
-V”027EC89B-2308-40EB-B6F5-3250946F992D”
-F”D:\DTS\20040829\dts1_Aug_23_2004__8_46_58_513P.dts” -E -!X ‘
output
————————————————————————–
DTSRun: Loading…
DTSRun: Executing…
NULL

exec master..xp_cmdshell ‘DTSRUN.exe -S”SQL” -N”dts1″
-V”39434FB0-80ED-4E37-9B4A-D7D4FBA9C54F”
-F”D:\DTS\20040829\dts1_Aug_28_2004__8_28_54_967A.dts” -E -!X ‘
output
————————————————————————–
DTSRun: Loading…
DTSRun: Executing…
NULL

exec master..xp_cmdshell ‘DTSRUN.exe -S”SQL” -N”MyImportDTS”
-V”7254C371-5AE0-49D4-9B1F-65F1BDC1D6BE”
-F”D:\DTS\20040829\MyImportDTS_Aug_28_2004__8_29_10_403A.dts” -E -!X ‘
output
————————————————————————–
DTSRun: Loading…
DTSRun: Executing…
NULL

exec master..xp_cmdshell ‘DTSRUN.exe -S”SQL” -N”MyImportDTS”
-V”A0288C2F-E779-46A6-82E9-D6970EE060DC”
-F”D:\DTS\20040829\MyImportDTS_Aug_28_2004__8_29_33_263A.dts” -E -!X ‘
output
————————————————————————–
DTSRun: Loading…
DTSRun: Executing…
NULL

Aug 29 2004 1:07PM
Save DTS packages – Completed



FIG 1.2

Conclusion

This article provides guidelines to SQL Server Database
Administrators on how to automatically save all DTS packages from a SQL Server
to many structured storage file. By scheduling this procedure to run daily all
of the packages would be saved to one folder on a daily basis.

»


See All Articles by Columnist
MAK

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles