February 22, 2010 at 7:38 am
Hello,
I am relative newbie with SSIS and was wondering if there is a way to zip or compress an output file from SSIS? I am grabbing data from a SQL Server db, putting it into an excel sheet and now I just want to compress it.
Thanks for any information.
February 22, 2010 at 7:42 am
WinZip has a command-line utility that I've used for that before. Call that from the package, and it'll do the job nicely.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 22, 2010 at 8:45 am
You could use one of the open source zip libraries from codeplex and do it in a script component. GSquared had a good idea, my only issue is that I hate call command-line tools, but that is me. Also, I think CozyRoc has a commercial component that does this.
CEWII
February 22, 2010 at 10:48 am
Thank you for your suggestions as they worked. I was also told on another forum about a way to do it with a visual basic Script Task, in case your interested.
Imports System
Imports System.Text
Imports System.IO
Imports System.IO.Compression
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum
Sub Main()
Call CompressFile("C:\test\test.csv", "C:\testew")
Call ConvertToByteArray(System.IO.File.ReadAllBytes("C:\\test\test.csv"))
End Sub
Public Function CompressFile(ByRef file As String, ByRef destination As String) As String
'Make sure user provided a valid file with path
If IO.File.Exists(file) = False Then
Return "Please specify a valid file (and path) to compress"
Exit Function
Else
'Make sure the destination directory exists
If IO.Directory.Exists(destination) = False Then
Return "Please provide a destination location"
Exit Function
End If
End If
Try
'Get just the name of the file
Dim name As String = Path.GetFileName(file)
'Convert the file to a byte array
Dim source() As Byte = System.IO.File.ReadAllBytes(file)
'Now we need to compress the byte array
Dim compressed() As Byte = ConvertToByteArray(source)
'Write the new file in the destination directory
System.IO.File.WriteAllBytes(destination & "\" & name & ".zip", compressed)
Return "Compression Successful!"
Catch ex As Exception
Return "Compression Error: " & ex.ToString()
End Try
End Function
Public Function ConvertToByteArray(ByVal source() As Byte) As Byte()
'Create a MemoryStrea
Dim memoryStream As New MemoryStream()
'Create a new GZipStream for holding the file bytes
Dim gZipStream As New GZipStream(memoryStream, CompressionMode.Compress, True)
'Write the bytes to the stream
gZipStream.Write(source, 0, source.Length)
gZipStream.Dispose()
memoryStream.Position = 0
'Create a byte array to act as our buffer
Dim buffer(memoryStream.Length) As Byte
'read the stream
memoryStream.Read(buffer, 0, buffer.Length)
'CLose & clean up
memoryStream.Dispose()
'Return the byte array
Return buffer
End Function
End Class
February 22, 2010 at 11:51 am
Were you able to unzip the file with winzip or equivalent tool?
CEWII
February 23, 2010 at 12:08 am
October 12, 2010 at 3:54 pm
using the same script above; but the result ZIP file is not formatted properly, it is corrupted... any ideas
October 29, 2010 at 12:41 am
If you are able to create a batch file for zip and unzip then use Execute process task and call batch file.
Thanks,
Ram
July 15, 2011 at 2:36 pm
hi all
I have a table in which I have different "buyer ID's" . they can be repetetive (1111122222333344444). 1 task: to extract each buyer in text file (Which is did with SSIS in for each loop. i had ) so i have those txt files in 1 folder (1.txt,2.txt,3.txt.4.txt) each txt file has all records of related buyer.
2 task : now i have to zip those files (using 7ZIp.).. i can zip them all together but i need to zip them indivisual.. for example; they should look like .(1.zip , 2.zip ,and so on)
can any1 help me on 2nd task..
thanks
Ankur
July 15, 2011 at 2:41 pm
This should be started as a new post, while somewhat similar to the existing thread it still belongs in its own.
Thanks.
CEWII
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply