Unpack zip files with SSIS Script Task. It is easier than you think.
This post is part of a larger project but the concepts in this post can stand alone. In this post, I will be demonstrating how to unpack a file using an SSIS script text. There are other methods for doing so. However, I need to unpack the file into a directory using the same filename as the original zip file. For example, if my zip file is named Test1234.zip. I need to unpack the file into a directory named Test1234. Other methods of unpacking, however, I need to place the contents back into a folder using the same name. Other methods simply unpacked the zip file.
To begin, verify you have the following project level variables or create it if it does not already exist.
Name Data Type Value
vSftpInboundFilePath String Staging directory - C:\Staging\
1. Drag a script task onto the canvas and name it appropriately.
2.Double click on the script task to open the script task editor. Verify that C# is the selected scripting language.
3. Identify the ReadOnlyVariables for the task.
4. Click on the "OK" button and return to the script task editor. It should now resemble the image below.
5. Click on "Edit Script" to enter the C# code editor and import the following namespaces for the project.
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO.Compression;
using System.IO;
#endregion
6. Enter the following code inside the entry point main().
public void Main()
{
//The source directory. The value is populated using the project level variable vSftpInboundFilePath.
string sourceDirectory = Dts.Variables["User::vSftpInboundFilePath"].Value.ToString();
//This variable stores the target or destination directory. The requirement for my project is that the zip file should be unpacked into the same
//directory as the zip file. Your requirement may differ.
string destinationDirectory = sourceDirectory;
try
{ //For loop to cycle through files in a directory.
foreach (string fileName in Directory.GetFiles(sourceDirectory))
{
//Get filename without extension.
string fileNameOnly = Path.GetFileNameWithoutExtension(fileName);
//Specify the directory we want to use as our target.
DirectoryInfo DestinationFolder = new DirectoryInfo(destinationDirectory);
//Create subfolder based off of original zip file name.
DirectoryInfo subFolder = DestinationFolder.CreateSubdirectory(fileNameOnly);
//Zip files will be unpacked. The files within the zipfile should be place in a normal
//folder using the same name as the zip file, without the .zip extension. The following lines of code
//capture the filename of the original zip file and will be used as the target for the files
//after unpacking the zip file.
//Unpack zip into our target directory.
System.IO.Compression.ZipFile.ExtractToDirectory(fileName, subFolder.ToString());
}
}
catch (Exception ex)
{
Dts.Events.FireError(0, "ERROR", ex.Message, null, 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
7. Save your code and exit the code editor. Click on the "OK" button on the subsequent window. Execute the script.
8. Execute the task.
In this example, we unpacked zip files in a staging directory. The unpacked folders were also placed in the staging directory, but we can very easily unpack them to another location. The folder structure remained intact and all of the files were successfully unpacked. In my next post, I will be demonstrating how to find a specific text file in the unpacked directory. I will also show you how to parse the file and make "updates" to the file. See you there. Please see below for the script code in its entirety.
Script Task 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;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO.Compression;
using System.IO;
#endregion
namespace ST_d5a2523cfb144e189df81da9a7c340b7
{
/// <summary>
/// ScriptMain is the entry point class of the script. Do not change the name, attributes,
/// or parent of this class.
/// </summary>
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
public void Main()
{
//The source directory. The value is populated using the project level variable vSftpInboundFilePath.
string sourceDirectory = Dts.Variables["User::vSftpInboundFilePath"].Value.ToString();
//This variable stores the target or destination directory. The requirement for my project is that the zip file should be unpacked into the same
//directory as the zip file. Your requirement may differ.
string destinationDirectory = sourceDirectory;
try
{ //For loop to cycle through files in a directory.
foreach (string fileName in Directory.GetFiles(sourceDirectory))
{
//Get filename without extension.
string fileNameOnly = Path.GetFileNameWithoutExtension(fileName);
//Specify the directory we want to use as our target.
DirectoryInfo DestinationFolder = new DirectoryInfo(destinationDirectory);
//Create subfolder based off of original zip file name.
DirectoryInfo subFolder = DestinationFolder.CreateSubdirectory(fileNameOnly);
//Zip files will be unpacked. The files within the zipfile should be place in a normal
//folder using the same name as the zip file, without the .zip extension. The following lines of code
//capture the filename of the original zip file and will be used as the target for the files
//after unpacking the zip file.
//Unpack zip into our target directory
System.IO.Compression.ZipFile.ExtractToDirectory(fileName, subFolder.ToString());
}
}
catch (Exception ex)
{
Dts.Events.FireError(0, "ERROR", ex.Message, null, 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
#region ScriptResults declaration
/// <summary>
/// This enum provides a convenient shorthand within the scope of this class for setting the
/// result of the script.
///
/// This code was generated automatically.
/// </summary>
enum ScriptResults {
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
}
}