CREATEPROCEDURE dbo.usp_SaveDTS

@DTSname varchar(256)='',

@Applicationpath varchar(700)='',

@destinationpath varchar(700)='\\Serverpath\Software\SQL 2000\DTS\',

@switches varchar(200)=' -E -!X '

AS

/*=============================================================================================================================

Stored Proc: usp_SaveDTS

Purpose: Saves DTS Packages as Structured Storage File

Author: Carolyn Richardson

Date: 25/09/2007

NOTES: Alter the @destinationpath to point to the correct UNC path to directory

=============================================================================================================================*/

SET  quoted_identifier off

SET nocount on

SET concat_null_yields_null off

DECLARE @Count int

,@folderexist int ,@maxcount int ,@query varchar(1000) ,@date varchar(10) ,@versionid varchar(40) ,@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 intidentity(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

END

ELSE

BEGIN

INSERTINTO  #DTSTABLE

(dtsname,versionid,createdate)

SELECT name,versionid,replace(replace(convert(varchar (25),createdate,109),':',' '),' ','_')

FROM msdb..sysdtspackages

WHERE name= @DTSname

END

IF(SELECTcount(*)FROM #dTStable)= 0

BEGIN

SET  @date =convert(varchar(100),getdate(),109)

PRINT  'Error: No valid DTS package found for saving'

END

ELSE

BEGIN

-- Check folder for Server Exists

SET  @destinationpath = @destinationpath +@@Servername CREATETABLE #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 BEGIN DELETE FROM #Files --Check folder for save date exists SET  @destinationpath = @destinationpath +'\'+@date 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 END SET @maxcount =(SELECTMAX(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 = "EXEC master..xp_cmdshell '" + @query + "'"

PRINT @query

EXEC(@query)

IF @@error<> 0

BEGIN

PRINT 'Error'

END

SET @Count = @Count+1

END

END

PRINT getdate()

PRINT 'Save DTS packages - Completed'

DROP TABLE #DTSTABLE

DROP TABLE #Files

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO