asked
Compress Via SSIS Script Task
I need to zip a file on a share such as \\Share\Directory$\BIGexcelfile.xls I see alot of info that sterrs us to using a 2rd pary zip via a command line or something. But in a large organization I need to stick to using native windows compression as it is a server I have no control over - does anyone have an example using the native windows compression - and Uh - MS - Hello - was this an oversight - did you not think about this before 2011? Why is this not a Task in SSIS using native tools?
Hi All, I didn't find any solution for my case. I am extracting different text files from one sql table based on different id's.(like 1.txt, 2.txt ,3.txt) Now I have to zip them indivisual in destination folder (like 1.zip, 2.zip , 3.zip etc) Using 7-zip. Is there any other method to zip. PLease help me. Ankur
Not sure what you've already tried... - Were you unable to bend the script by @TRAD to your needs? - @Pavel Pawlowski linked to two previous answers that specifically called out 7-zip for compression from an SSIS execute process task. Did that approach give errors? If these weren't what you were looking for, you may need to reword your question so we can better understand the objective.
Hi KenJ 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) I hope this can help you to understand. Ankur
Hi KenJ I didn't find Pavel's answer. I tried everythng but coudn't succeed. Can you post Pavel's answer here.Or if you know better approach . please tell me. Its urgent for me. Ankur
There is similar question [SSIS and Decompression][1]. You can find out detail there as it is the same but reversed. Also this question is related to compression: [File Compression SSIS][2]. As @KenJ posted in his answer, probably the only possibility to compress/decompress if you cannot install anything on the server will be writing a SSIS script which will use classes frm the system.io.compression namespace. [1]:
http://ask.sqlservercentral.com/questions/35077/ssis-and-decompression [2]:
http://ask.sqlservercentral.com/questions/31411/file-compression-ssis
If using only microsoft technology is a firm requirement, you can add an execute process task to call the [compact][1] command. This would compress it in the file system, but I doubt it would remain compressed if you were to ftp or email it. Something to roll it up into a zip archive would really be the way to go. [1]:
http://technet.microsoft.com/en-us/library/bb490884.aspx
Thanks KenJ But Pavel's answer is not working for me. I tried with script task but I am not good in writing script so couldn't do that too. Is there any any ssis way i can zip files individauly. Or some screen shots anythng which will help me. I have to do everything dynamically. I am not able to do statically .I don't how would I do dynamically. Please help me Ankur
Here is a little bit of code that I use to zip up data using the native windows compression tool. "compress files and folders" I just execute this in a SQL job using the type "Active X Script" and use VBScript as the Language. The comments in the code should be pretty self explanatory for changing what you are zipping up and where you want to place it. 'Create the Shell.Application object Dim oShellApp, oFSO, oWScript Set oShellApp = CreateObject("Shell.Application") 'Create the File System object SET oFSO = CreateObject("Scripting.FileSystemObject") DIM sZipFile sZipFile = "C:\WORK\ZIPTEST\ZIPTHIS.zip" sTargetFolder = "C:\WORK\ZIPTEST\ZIPTHIS" oFSO.CreateTextFile sZipFile, True '.Write "PK" & Chr(5) & Chr(6) & String(18, vbNullChar) oShellApp.NameSpace(sZipFile).CopyHere oShellApp.NameSpace(sTargetFolder).Items(),1556 SET oShellApp = nothing SET oFSO = nothing
1. New Package 2. New string variable with package scope - `CurrentFile` 3. New Foreach Loop Container 4. Edit Foreach Loop container - Collection Tab -> Folder = `fullPathToFolderWithTextFiles` - Variable Mappings Tab -> `User::CurrentFile` maps to Index 0 5. New Execute Process Task inside the Foreach Loop container 6. Edit Execute Process Task - Process Tab -> Executable = `fullPathOf7z.exeFile` - Process Tab -> Working Directory = `fullPathToFolderWith7z.exe` - Expressions -> For Property "Arguments" set Expression = `"a " + @[User::CurrentFile] + ".zip " + @[User::CurrentFile]` 7. Save and Run Package
Once you have accomplished a task, such as compressing from SSIS, you can usually solve similar problems with a few adjustments. In this case, the key is in the 7zip command line. You can learn more about 7zip command line usage here -
http://www.dotnetperls.com/7-zip-examples Once you've found out how to use 7zip to compress a directory rather than a file, you'll need to adjust your package. you won't need a loop this time because you're only compressing one thing: the directory. Instead of the foreach loop you used for compressing individual files (or perhaps after the loop if you're using the same package), you will just want an execute process task. Instead of passing a filename to 7zip, you'll pass it a directory (ending in the \ character). You might use an appropriately named variable, such as CurrentDirectory.
thanks KenJ I tried that approach .but its zipping the whole directory not the individual folders. - Ankur - -
