Case
There is an upload and download task in the SSIS Azure Pack, but how can I delete a storage container in my Azure strorage account that was created with SSIS?
Solution
At the moment there is no Azure File System Task for SSIS, but you can also do this with a Script Task.
1) Azure SDK
First download and install the Azure SDK for .NET 2.7 (or newer). This SDK contains an assembly that we need to reference in our Script Task. When you hit the download button you can download multiple files. The one you need is called MicrosoftAzureLibsForNet-x64.msi (you can't install both 64 and 32bit).
 |
| Libraries only is enough |
2) SSIS Feature Pack for Microsoft Azure
Download and install (next, next, finish) the SSIS Feature Pack for Microsoft Azure (
2012,
2014).
 |
| SSIS Azure Feature Pack |
3 Package Parameters
Unfortunately we cannot use the Azure Storage Connection Manager because the properties we need are sensitive (writeonly in a Script Task), therefore we will use two string package parameters. The first one contains the name of the container that you want to delete and is called "ContainerName". You can find the exact name in the Azure management portal.
 |
| Container in Storage Account |
The second package parameter is a sensitive string parameter named "ConnectionStringStorageAccount". It contains the connection string of the Azure Storage Account. The format should be like this (you have to replace the red parts):
DefaultEndpointsProtocol=https;AccountName=
ssisjoost;AccountKey=
34PQgq+Kpr9Mz4rUfGoTpR1GZrGcC/SaFphXt3aUmgzXrcowtba0vz+uq1bIYBS5FkFYEaJ6W2CYVSsB5C8AEDQ==
The first red part of the string is the name of the storage account. You can look it up on the Azure management portal.
 |
| Storage Account "ssisjoost" |
The second red part is the Account Access Key which can also be copied from Azure.
 |
| Storage Account Access Keys |
The end result should look like this. Of course you can use different names or project parameters instead, but then you have to change that in the Script Task!
 |
| Package Parameters |
4) Add Script Task
Add a Script Task to the Control Flow and give it a suitable name like "SCR - Delete Storage Container". Edit it, choose the ScriptLanguage and select the two string parameters from the previous step as ReadOnlyVariables. Then click on the Edit Script button to open the VSTA environment.
 |
| Edit Script Task |
5) Add reference
In the solution explorer we first need to add a reference to one of the assemblies installed in step 1: Microsoft.Windows.Storage.dll which is located in the folder: C:\Program Files\Microsoft SDKs\Azure\.NET SDK\v2.7\ToolsRef\
 |
| Adding a reference in C# |
6) The code - Import custom namespaces
To shorten the code we need to add some usings (C#) or some imports (VB). Add these just below the standard imports or usings.
// C# Code
#region CustomNamespaces
using Microsoft.WindowsAzure;
using Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.Storage.Auth;
using Microsoft.WindowsAzure.Storage.Blob;
#endregion
or VB.NET code
' VB.NET Code
#Region "CustomImports"
Imports Microsoft.WindowsAzure
Imports Microsoft.WindowsAzure.Storage
Imports Microsoft.WindowsAzure.Storage.Auth
Imports Microsoft.WindowsAzure.Storage.Blob
#End Region
7) The code Main method
In the main method we need to replace the existing comments and code with the following code.
// C# Code
public void Main()
{
// Get parameter values. Notice the difference between
// a normal and a sensitive parameter to get its value
string connStr = Dts.Variables["$Package::ConnectionStringStorageAccount"].GetSensitiveValue().ToString();
string containerName = Dts.Variables["$Package::ContainerName"].Value.ToString();
try
{
// Retrieve storage account from connection string.
CloudStorageAccount storageAccount = CloudStorageAccount.Parse(connStr);
// Create the blob client.
CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient();
// Create a reference to the container you want to delete
CloudBlobContainer container = blobClient.GetContainerReference(containerName);
// Delete the container if it exists
container.DeleteIfExists();
// Show success in log
bool fireAgain = true;
Dts.Events.FireInformation(0, "Delete Storage Container", "Container " + containerName + " was deleted successfully", string.Empty, 0, ref fireAgain);
// Close Script Task with Success
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception ex)
{
// Show Failure in log
Dts.Events.FireError(0, "Delete Storage Container", ex.Message, string.Empty, 0);
// Close Script Task with Failure
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
or VB.NET code
' VB.NET Code
Public Sub Main()
' Get parameter values. Notice the difference between
' a normal and a sensitive parameter to get its value
Dim connStr As String = Dts.Variables("$Package::ConnectionStringStorageAccount").GetSensitiveValue().ToString()
Dim containerName As String = Dts.Variables("$Package::ContainerName").Value.ToString()
Try
' Retrieve storage account from connection string.
Dim storageAccount As CloudStorageAccount = CloudStorageAccount.Parse(connStr)
' Create the blob client.
Dim blobClient As CloudBlobClient = storageAccount.CreateCloudBlobClient()
' Create a reference to the container you want to delete
Dim container As CloudBlobContainer = blobClient.GetContainerReference(containerName)
' Delete the container if it exists
container.DeleteIfExists()
' Show success in log
Dim fireAgain As Boolean = True
Dts.Events.FireInformation(0, "Delete Storage Container", "Container " + containerName + " was deleted successfully", String.Empty, 0, fireAgain)
' Close Script Task with Success
Dts.TaskResult = ScriptResults.Success
Catch ex As Exception
' Show Failure in log
Dts.Events.FireError(0, "Delete Storage Container", ex.Message, String.Empty, 0)
' Close Script Task with Failure
Dts.TaskResult = ScriptResults.Failure
End Try
End Sub
