Can you zip files from a DTS Package?

[Glowworm27]

Hello all,

I am creating a DTS package to offload/Archive Old Data from the SQL Server, and I have it creating csv files for the archive. Is there anyway to Zip these files up as a task in the package?

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[Glowworm27]

Also anyway to delete files from DTS?

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[MDXer]

I do both of the above tasks with xp_cmdshell the ability the cmd to zip would depend on the compression app you use.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[cbhnp]

I use a xp_cmdshell and a command line interface to winzip to zip files.

To move, copy, delete files use an ActiveX Script Task and the FileSystemObject.

 

[Glowworm27]

Thanks for all the support.

I have written these two stored procedures to zip and unzip files using winzip command line application. (winzip needs to be registered to perform the unzip from the command line, otherwise it will display the NAG screen)

here are the two procedures

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

ALTER   PrOCEDURE [dbo].[sp_UnZipArchive] 
	@ZipFileLocation   	Varchar(300),
	@ArchiveDestination 	VarChar(300),
	@DeleteZipAfterUnZip	char(5) = Null

AS

declare @SQLCommand  VarChar(400)


SET @SQLCommand = 
'exec master..xp_cmdshell ' + '''' + 'E:\Shares\mssql7\winzip9\WZUNZIP '
+ @ZipFileLocation  + ' ' + @ArchiveDestination +    ''''

EXEC (@SQLCommand)


if @DeleteZipAfterUnZip is Not Null
Begin
	if @DeleteZipAfterUnZip = 'True'
	BEgin
	 	SET @SQLCommand = 'exec master..xp_cmdshell ' + '''' + 'Del ' + @ZipFileLocation + ''''

		EXEC (@SQLCommand)
		--print @sqlcommand
	End
End


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

and the Zip routine

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO


ALTER     PrOCEDURE [dbo].[sp_ZipArchive] 
	@ArchiveLocation   	Varchar(300),  --Folder where the files to be zipped reside
	@ZipFileDestination 	VarChar(300),	
	@ZipFileName 		varchar(50),
	@DeleteAfterZip		char(5) = Null

AS

declare @SQLCommand  VarChar(400)

--Add the .zip extension to the filename
SET @ZipFileName = @ZipFileName + '.zip'

SET @SQLCommand = 
'exec master..xp_cmdshell ' + '''' + 'E:\Shares\mssql7\winzip9\wzzip '
+ @ZipFileDestination + @ZipFileName + ' ' + @ArchiveLocation + '*.csv ' +    ''''

EXEC (@SQLCommand)


if @deleteAfterZip is Not Null
Begin
	if @DeleteAfterZip = 'True'
	BEgin
	 	SET @SQLCommand = 'exec master..xp_cmdshell ' + '''' + 'Del ' + @ArchiveLocation + '*.csv ' + ''''
-- I am zipping CSV files, you can change the procedure to accept all files in the folder 
		EXEC (@SQLCommand)
		--print @sqlcommand
	End
End


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Hope someone can use this in the future


George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[MDXer]

I'm not saying winzip isn't a great tool but I reccomend people look at winrar. Both for file size and compression ratios. Winzip usually starts having issues in the 1-2gig range and if I remember correctly it doesn't go beyong 2 gigs, RAR easily handles files in 100s of gigs. Compression is where you see a big diffrence with rar you can see compression as high as 95% taking those 100meg files down to much more managable sizes. I have brought winrar into every shop I have started and they quickly dump winzip after a few uses.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[Glowworm27]

I was unaware the winrar, had a command line, thanks.

Is it also Shareware-crippled? like winzip?

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[mrdenny]

There is a much faster command line tool for compressing files with .zip. PKWare.com still have pkzip and pkunzip.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[MDXer]

winrar is not disabled in the shareware.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck