March 7, 2008 at 6:42 pm
Hi All,
Iam New to this forum, Can any one please tell how to Zip two or more files Using Script Task.
Thank You,
-Raghav.
March 8, 2008 at 9:53 am
You need a zip program because you cannot do this with the windows compressed folders. You then use a foreach loop to loop through the files in a directory. You then a sql script task with xp_cmdshell to perform the zip operation.
I am sure there are other methods, but this is the first one that came to my mind.
March 8, 2008 at 11:23 pm
master..xp_cmdshell 'pkzipc -add drivename\path\filename.zip' drivename\path\filetozip '
Provided pkzip is installed on the server.
March 10, 2008 at 10:56 am
HI,
I can compress a single file but I want to couple of files to the same Zip file. Here is my code from the script task.
Option Strict Off
Imports System
Imports System.Collections.Generic
Imports System.IO
Imports System.IO.Compression
Public Class CompressionSnippet
Public Shared Sub Main()
Dim Path As String = "C:\Util\Pregnancy.xls"
Dim Path1 As String = "C:\Util\Pregnancy1.xls"
Dim Destination As String = "C:\Util"
'Console.WriteLine("Contents of {0}", Path)
'Console.WriteLine(File.ReadAllText(Path))
'Console.WriteLine("Contents of {0}", Path1)
'Console.WriteLine(File.ReadAllText(Path1))
CompressFile(Path1, Path)
'Console.WriteLine()
''UncompressFile(Path + ".gz")
'Console.WriteLine()
'Console.WriteLine("Contents of {0}", Path + ".zip.txt")
'Console.WriteLine(File.ReadAllText(Path + ".zip.txt"))
End Sub
Public Shared Sub CompressFile(ByVal Path1 As String, ByVal Path As String)
Dim sourceFile As FileStream = File.OpenRead(Path)
Dim sourceFile1 As FileStream = File.OpenRead(Path1)
Dim destinationFile As FileStream = File.Create(Path1 + ".gz")
Dim buffer(sourceFile.Length()) As Byte
sourceFile.Read(buffer, 0, buffer.Length)
Dim buffer1(sourceFile1.Length()) As Byte
sourceFile1.Read(buffer1, 0, buffer1.Length)
Using output As New GZipStream(destinationFile, _
CompressionMode.Compress)
'Console.WriteLine("Compressing {0} to {1}.", sourceFile.Name, _
' destinationFile.Name, False)
'output.Write(buffer, 0, buffer.Length)
Console.WriteLine("Compressing {0} to {1}.", sourceFile1.Name, _
destinationFile.Name, False)
output.Write(buffer1, 0, buffer1.Length)
End Using
' Close the files.
sourceFile.Close()
sourceFile1.Close()
destinationFile.Close()
End Sub
'Public Shared Sub UncompressFile(ByVal path As String)
' Dim sourceFile As FileStream = File.OpenRead(path)
' Dim destinationFile As FileStream = File.Create(path + ".txt")
' ' Because the uncompressed size of the file is unknown,
' ' we are imports an arbitrary buffer size.
' Dim buffer(4096) As Byte
' Dim n As Integer
' Using input As New GZipStream(sourceFile, _
' CompressionMode.Decompress, False)
' Console.WriteLine("Decompressing {0} to {1}.", sourceFile.Name, _
' destinationFile.Name)
' n = input.Read(buffer, 0, buffer.Length)
' destinationFile.Write(buffer, 0, n)
' End Using
' ' Close the files.
' sourceFile.Close()
' destinationFile.Close()
'End Sub
End Class
March 10, 2008 at 11:20 am
An example using the ICSSharpZipLib (http://www.icsharpcode.net/OpenSource/SharpZipLib/[/url])
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Imports ICSharpCode.SharpZipLib.Core
Imports ICSharpCode.SharpZipLib.Zip
Public Class ScriptMain
REM Author: Tommy Bollhofer (thomas_bollhofer@symantec.com)
REM Last Modified: 03/12/2008
Public Sub Main()
Dim strFilePath As String
Dim strZipFileName As String
strFilePath = Dts.Variables("varFilePath").Value.ToString
strZipFileName = Dts.Variables("varZipFileName").Value.ToString
REM For Debugging Purposes
'System.Windows.Forms.MessageBox.Show(strFilePath & "\" & strFileName)
Dim strFileNames As Array = Directory.GetFiles(strFilePath & "\")
Dim ZipOutputStream As ZipOutputStream
ZipOutputStream = New ZipOutputStream(File.Create(strFilePath & "\" & strZipFileName))
REM Compression Level: 0-9
REM 0: no(Compression)
REM 9: maximum compression
ZipOutputStream.SetLevel(9)
Dim strFile As String
Dim BufferSize As Integer = 4096
For Each strFile In strFileNames
Dim objStreamFile As FileStream = File.OpenRead(strFile)
Dim objZipEntry As ZipEntry = New ZipEntry(Path.GetFileName(strFile))
objZipEntry.DateTime = DateTime.Now
objZipEntry.Size = objStreamFile.Length
ZipOutputStream.PutNextEntry(objZipEntry)
Dim ZipOutputBuffer(BufferSize) As Byte
Dim TotalBytes As Integer = objStreamFile.Read(ZipOutputBuffer, 0, BufferSize)
While TotalBytes > 0
REM For Debugging Purposes
'Console.WriteLine(TotalBytes)
'Console.WriteLine(Size)
'Console.WriteLine(System.Text.UnicodeEncoding.ASCII.GetString(ZipOutputBuffer))
'Console.WriteLine("---------------------")
ZipOutputStream.Write(ZipOutputBuffer, 0, TotalBytes)
TotalBytes = objStreamFile.Read(ZipOutputBuffer, 0, BufferSize)
End While
objStreamFile.Close()
objStreamFile = Nothing
Next
ZipOutputStream.Finish()
ZipOutputStream.Close()
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
Tommy
Follow @sqlscribeMay 13, 2008 at 1:07 pm
hi Adam, so there is no way to use a sql task to zip using windows?
May 13, 2008 at 5:08 pm
DBA (5/13/2008)
hi Adam, so there is no way to use a sql task to zip using windows?
Windows does not have a command line utility to unzip files. You will have to use pkzip, winzip or something of this nature. To call this zip function from TSQL means you have to use XP_CMDSHELL.
If you create a job you can use an os command to issue the zip statement. Both methods require that you have a zip utility with command line functionality.
Links to products:
May 14, 2008 at 7:00 am
thanks!
May 14, 2008 at 8:45 am
do i need special permissions to run the XP_CMDSHELL? Its weird when i execute it from my machine it works but when i put it in a job it doesn't
May 14, 2008 at 12:28 pm
You should make sure the service accounts have access to the directory. The service accounts are SQL Server and SQL Server Agent.
January 8, 2011 at 4:10 am
raghav.gurram (3/7/2008)
Hi All,Iam New to this forum, Can any one please tell how to Zip two or more files Using Script Task.
Thank You,
-Raghav.
I use the Microsoft Visual J# Redistributable Packages the zip a file in a Script Task with C#: http://microsoft-ssis.blogspot.com/2011/01/zip-sourcefile-to-archive.html
The advantage is that my clients don't see it as Third Party software (they often don't like open source or third party dll's on their production server).
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply