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).
2) SSIS Feature Pack for Microsoft Azure
Download and install (next, next, finish) the SSIS Feature Pack for Microsoft Azure (2012, 2014).
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.
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.
The second red part is the Account Access Key which can also be copied from Azure.
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!
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.
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\
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.
or VB.NET code
7) The code Main method
In the main method we need to replace the existing comments and code with the following code.
or VB.NET code
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.
1
2
3
4
5
6
7
| // C# Code#region CustomNamespacesusing Microsoft.WindowsAzure;using Microsoft.WindowsAzure.Storage;using Microsoft.WindowsAzure.Storage.Auth;using Microsoft.WindowsAzure.Storage.Blob;#endregion |
or VB.NET code
1
2
3
4
5
6
7
| ' VB.NET Code#Region "CustomImports"Imports Microsoft.WindowsAzureImports Microsoft.WindowsAzure.StorageImports Microsoft.WindowsAzure.Storage.AuthImports 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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
| // C# Codepublic 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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
| ' VB.NET CodePublic 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 TryEnd Sub |
No comments:
Post a Comment