How to use Wildcards to Unzip Through SSIS Script Task

Feb 15, 2011 at 5:03 PM
Edited Feb 15, 2011 at 6:32 PM

I am using version 1.9.1.5 of Ionic.Zip. I have an SSIS 2008 script task, where an attempt is made to unzip one or more zip files in a specific directory out to the same directory. The problem I am having is that when the code runs, an exception is thrown that there are 'illegal characters in the path'. The zip files have the following naming convention: SmartExport_DW2001.02.01.zip. I have a feeling this is happening because I am not impelmenting the use of wildcards correctly. The following is my code in the script task:

using System;
using System.Data;
using Ionic.Zip;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_1cc80e7e0e4443f3815ad62038cbef3a.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion


        public void Main()
        
        {
            try
            {
                Variables vars = Dts.Variables;
                String zipFile = vars["ZipFileProcessQueueLocationWildCard"].Value.ToString();
                String unpackDirectory = vars["ZipFileProcessQueueLocation"].Value.ToString();
                //String unpackDirectory = Dts.Variables["User::ZipFileProcessQueueLocation"].Value.ToString();
                ZipFile zip = ZipFile.Read(zipFile);
                    foreach (ZipEntry e in zip)
                    {
                        e.Extract(unpackDirectory, ExtractExistingFileAction.OverwriteSilently);
                    }
                Dts.TaskResult = (int)ScriptResults.Success;
            }
            catch
            {
                throw;
            }
           
        }
    }
}

 

 

 The package variable ZipFileProcessQueueLocationWildCard resolves to R:\CasambaRefresh\DWExport_ProcessQueue\*.zip.  The variable ZipFileProcessQueueLocation resolves to R:\CasambaRefresh\DWExport_ProcessQueue\. The intent here is to unzip any file with a .zip extension in the directory.

If I replace *.zip with a hard coded valid file name, the code works. That is, the end result is that the zip file is unzipped, and its contents are written to the correct location.

What am I doing wrong?

The complete error message is as follows:

OnError,ORACLE,ADVOCAT_NT\cdunn,Unzip,{C362B4AF-46D4-42EA-9DF5-23B65FD72C13},{86DA484C-03B2-4C72-ABC7-DF3CF8476FB6},2/15/2011 12:06:01 PM,2/15/2011 12:06:01 PM,1,0x,System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.ArgumentException: Illegal characters in path.
   at System.Security.Permissions.FileIOPermission.HasIllegalCharacters(String[] str)
   at System.Security.Permissions.FileIOPermission.AddPathList(FileIOPermissionAccess access, AccessControlActions control, String[] pathListOrig, Boolean checkForDuplicates, Boolean needFullPath, Boolean copyPathList)
   at System.Security.Permissions.FileIOPermission..ctor(FileIOPermissionAccess access, AccessControlActions control, String[] pathList, Boolean checkForDuplicates, Boolean needFullPath)
   at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)
   at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share)
   at Ionic.Zip.ZipFile.get_ReadStream()
   at Ionic.Zip.ZipFile.ReadIntoInstance(ZipFile zf)
   at Ionic.Zip.ZipFile.Read(String fileName, TextWriter statusMessageWriter, Encoding encoding, EventHandler`1 readProgress)
   at Ionic.Zip.ZipFile.Read(String fileName)
   at ST_1cc80e7e0e4443f3815ad62038cbef3a.csproj.ScriptMain.Main()
   --- End of inner exception stack trace ---
   at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
   at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
   at System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, CultureInfo culture)
   at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()
OnError,ORACLE,ADVOCAT_NT\cdunn,CasambaRefresh_ProcessZipFiles,{DC446960-1FE8-43DE-A2C0-D88902ABACAC},{86DA484C-03B2-4C72-ABC7-DF3CF8476FB6},2/15/2011 12:06:01 PM,2/15/2011 12:06:01 PM,1,0x,System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.ArgumentException: Illegal characters in path.
   at System.Security.Permissions.FileIOPermission.HasIllegalCharacters(String[] str)
   at System.Security.Permissions.FileIOPermission.AddPathList(FileIOPermissionAccess access, AccessControlActions control, String[] pathListOrig, Boolean checkForDuplicates, Boolean needFullPath, Boolean copyPathList)
   at System.Security.Permissions.FileIOPermission..ctor(FileIOPermissionAccess access, AccessControlActions control, String[] pathList, Boolean checkForDuplicates, Boolean needFullPath)
   at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)
   at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share)
   at Ionic.Zip.ZipFile.get_ReadStream()
   at Ionic.Zip.ZipFile.ReadIntoInstance(ZipFile zf)
   at Ionic.Zip.ZipFile.Read(String fileName, TextWriter statusMessageWriter, Encoding encoding, EventHandler`1 readProgress)
   at Ionic.Zip.ZipFile.Read(String fileName)
   at ST_1cc80e7e0e4443f3815ad62038cbef3a.csproj.ScriptMain.Main()
   --- End of inner exception stack trace ---
   at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
   at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
   at System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, CultureInfo culture)
   at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()
OnTaskFailed,ORACLE,ADVOCAT_NT\cdunn,Unzip,{C362B4AF-46D4-42EA-9DF5-23B65FD72C13},{86DA484C-03B2-4C72-ABC7-DF3CF8476FB6},2/15/2011 12:06:01 PM,2/15/2011 12:06:01 PM,0,0x,(null)
PackageEnd,ORACLE,ADVOCAT_NT\cdunn,CasambaRefresh_ProcessZipFiles,{DC446960-1FE8-43DE-A2C0-D88902ABACAC},{86DA484C-03B2-4C72-ABC7-DF3CF8476FB6},2/15/2011 12:06:01 PM,2/15/2011 12:06:01 PM,1,0x,End of package execution.

Thank you for your help!

cdun2

Coordinator
Feb 15, 2011 at 9:21 PM

Here's what you're doing wrong: the ZipFile constructor does not accept wildcards.

You need to resolve that wildcard into an actual filename, and pass that filename into the ZipFile constructor.  Do this with Directory.GetFiles().  Also, you should use a using() clause.  Always. Always. Always. The code looks like this:


        Variables vars = Dts.Variables;
        String wildcard = vars["ZipFileProcessQueueLocationWildCard"].Value.ToString();
        String unpackDirectory = vars["ZipFileProcessQueueLocation"].Value.ToString();
        var files = Directory.GetFiles(wildcard);
        if (files!= null && files.Length >0)
        {
            string zipFile = files[0];
            using (ZipFile zip = ZipFile.Read(zipFile))
            {
                foreach (ZipEntry e in zip)
                {
                    e.Extract(unpackDirectory, ExtractExistingFileAction.OverwriteSilently);
                }
            }
        }
        Dts.TaskResult = (int)ScriptResults.Success;

This resolves the wildcard, then extracts the first file in the list.  In the general case, the wildcard will resolve into a list of more than one filename, and in that case you might want to modify the code to do a for loop around the using/extract loop.