question

NickRyanNZ-4337 avatar image
0 Votes"
NickRyanNZ-4337 asked NickRyanNZ-4337 commented

Exception when calling ZipFile.ExtractToDirectory from a Script Task

I've made myself a very simple package as a proof of concept. It calls a script task to unzip all the contents of a zip file. I've hard-coded the values for source zip and target directory.

The task fails with this (to me) very unhelpful error message.

87369-image.png



I'm not a C# programmer so I just copied the code from a page I now can't find.

This is my code:

 #region Help:  Introduction to the script task
 /* The Script Task allows you to perform virtually any operation that can be accomplished in
  * a .Net application within the context of an Integration Services control flow. 
  * 
  * Expand the other regions which have "Help" prefixes for examples of specific ways to use
  * Integration Services features within this script task. */
 #endregion
    
    
 #region Namespaces
 using System.IO.Compression;
 #endregion
    
 namespace ST_7705c64ac5b442f6bd8a15993d3d2168
 {
     [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
     public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
     {
         public void Main()
         {
             string zipFilename = "\\<server name>\\MBSMIS\\Transfers\\IN\\Exonet\\Statements\\Statements.zip";
             string targetDirectory = "\\<server name>\\MBSMIS\\Transfers\\IN\\Exonet\\Statements\\my-output-folder";
    
             ZipFile.ExtractToDirectory(zipFilename, targetDirectory);
    
             Dts.TaskResult = (int)ScriptResults.Success;
         }
         #region ScriptResults declaration
         enum ScriptResults
         {
             Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
             Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
         };
         #endregion
     }
 }

I have included a reference to System.IO.Compression.FileSystem which is probably a given as I don't think it compiles without that.

I've checked things like making sure the strings have the correct path and file-name and that the archive is not open anywhere else.

Is there a method of getting more helpful information out of the error or does that mean something to people more familiar with C#?

sql-server-integration-services
image.png (30.1 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered NickRyanNZ-4337 commented

Hi @NickRyanNZ-4337,

UNC file share should be like \\server\share\dir1\dir2

Please try the following in SSIS Script Task.

 void Main()
 {
     bool rc = true;
        
     // UNC file share should be like \\server\share\dir1\dir2
     string zipFilename = @"\\server name\MBSMIS\Transfers\IN\Exonet\Statements\Statements.zip";
     string targetDirectory = @"\\server name\MBSMIS\Transfers\IN\Exonet\Statements\my-output-folder";
    
     try
     {
         ZipFile.ExtractToDirectory(zipFilename, targetDirectory);
     }
     catch (Exception ex)
     {
         rc = false;
    
         // Don't forget to tick/check off System::TaskName variable as ReadOnly in the Task Parameters
         Dts.Events.FireError(18, Dts.Variables["System::TaskName"].Value.ToString()
           , ex.Message.ToString()
           , "", 0);
     }
    
     Dts.TaskResult = (rc) ? (int)ScriptResults.Success : (int)ScriptResults.Failure;
 }
· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Yitzhak

To my rescue, again. Thank you.

I did have the server name correctly. Enclosing the word in <> was simply to try and indicate it was a placeholder. I'm assuming that it's not wise to put SQL Server names in a public forum.

I guess it was the @ sign I was missing?

Anyway, all sorted. Now to flesh it out with the rest of what's required.

Cheers,
Nick

0 Votes 0 ·

Oh, sorry, one more question. The documentaton states that the directory should not exist. Is there a way to tell it to overwrite if it does?

0 Votes 0 ·

The official MS documentation: system.io.compression.zipfile.extracttodirectory
has an overload ExtractToDirectory(String, String, Boolean) method, where 3rd parameter is true to overwrite files; false otherwise.
Please give it a shot.



0 Votes 0 ·
Show more comments