Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Sep 15, 2004

Automating "Save DTS package"

By Muthusamy Anantha Kumar aka The MAK

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date