Post reply

SSIS - Package to Zip contents of a folder into one .zip file

  • August 12, 2015 at 4:19 am

    #320453

    Hi gang,

    I have a requirement to create a package that takes all files in a given folder and adds them to a single archive (.zip) file. I've tried several methods using 7zip and while I can create archives for every file in the directory I can't seem to get them into a single .zip file.

    Can anyone help?

    Many thanks,

    Neil

  • August 12, 2015 at 5:26 am

    #1819262

    What methods have you tried?

    LinkedIn

  • August 12, 2015 at 5:30 am

    #1819263

    Several variations on a theme either using script task and variables or execute process task. Both worked in terms of getting files archived however they made individual archives and not one single archive with all the files contained therein.

  • August 12, 2015 at 5:39 am

    #1819265

    Have you tried something like this? http://qa.sqlservercentral.com/articles/Integration+Services+(SSIS)/64028/

    I'm 99% sure I've done it before using an execute process task in a foreach loop container, but I'm afraid I don't have the package anymore

    LinkedIn

  • August 12, 2015 at 5:44 am

    #1819267

    I'll give that a go. Thanks for your help xyoung much appreciated! 🙂

  • August 12, 2015 at 6:07 am

    #1819275

    Ah...I currently have SQL 2008 which only has Visual Basic 2008 and not VB .NET. Have to confess I am not up on VB so not sure if it's possible to amend the script to work with 2008?

    Any ideas?

    Thanks,

    N

  • August 12, 2015 at 6:39 am

    #1819292

    I'm not a vb guru either I'm afraid, do you get any errors if you just copy in the procedure and leave the rest as is? (I.e Public Sub Main()....End Sub)

    Alternatively, try:

    1. Create string variable 'File' (if folder name is dynamic e.g dated then perhaps make a variable for this too and set the expression as required)

    2. Create foreach loop container

    3. On 'Collection' tab set it to file enumerator

    4. Still on 'Collection' tab under 'Enumerator configuration' choose your folder (if your folder name is dynamic you will need to put your variable in 'expressions')

    5. For the file put *.*

    6. On 'Variables' tab map your string variable 'File', index 0

    7. Add an 'Execute Process' task in the container itself

    8. Set up the executable, utilise the 'File' variable (I think build an expression for 'Arguments', use the arguments you need, file variable and then folder path)

    Sorry it's been a little while since I've done this and I don't have the tools available to test it out, but hopefully that will help you in some way!

    LinkedIn

  • August 12, 2015 at 7:00 am

    #1819302

    Thanks again xyoung!

    When I copy the script in from Catherine's article the dts.variable is not recognised so the whole thing fails.

    I'm not sure if I can declare these variables in the script manually? Or if there is a reference I can add to allow these variables to be referenced.

    I will keep looking.

    Thanks again 🙂

  • August 12, 2015 at 7:24 am

    #1819309

    Do you have this at the top of your script:

    Imports Microsoft.SqlServer.Dts.Runtime

    LinkedIn

  • August 12, 2015 at 8:23 am

    #1819329

    It wasn't but I added it at the top, but sadly the dts parts of the code are still erroring.

    Thanks,

    N

  • August 13, 2015 at 1:03 am

    #1819566

    Have you installed the sql server client tools, notably the visual studio shell part because that is what turns ordinary visual studio into the version for sql ssis, ssas and ssrs and adds the dts runtime etc.?

    Also do you have a zip program such as 7-zip installed as you can use the execute process task to run it with a command line which is how we use it?

    You'll need it on both your local development pc and the final server the package will execute on - and in the same location!

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply