Moving file from one folder to another in Azure Blob Storage using SSIS

In this post I'll explain how we can move a file from a folder in Azure Blob Storage Container to another folder in the same Container, and delete the original file.

Lets assume the Blob Container name is BlobContainer and the folder names are Folder1 and Folder2. Assume the file name as BlobFile.csv
File will be moved from one folder to another based on the file URL, by using WebClient in C#.

You can generate the File URL as below
BlobStorageURL=https://blobstorage.blob.core.windows.net
(blobstorage is the account name)

Then existing file URL is
https://blobstorage.blob.core.windows.net/BlobContainer/Folder1/BlobFile.csv

URL for the move file is
https://blobstorage.blob.core.windows.net/BlobContainer/Folder2/BlobFile.csv

As I mentioned in my previous post, you can get the file name of the file, available in the Azure Blob Storage using a Foreach Loop Container and possible to set those File URL values based on the file name.

Addition to above we need to know the Shared Access Signature value for each folder.
If you do not have those values, you can get the Shared Access Signature value as below.
To get it for Folder1, go to the Folder1 using Microsoft Azure Storage Explorer and right click on Folder1.
Then select Get Shared Access Signature.

Then you will get a window as below.

In that first things is make sure that you have provided the Expiry time accordingly.
Then put the tick on Write and Delete as well, since it is needed for our requirement.
After that click on Create and you will get the below window

To get the Shared Access Signature value, click on the Copy button under Query string.
The best things is to have that value in a Project Parameter in SSIS. Then when ever needed, you will be able to easily change it.

Follow the same steps for Folder2 as well.

As a best practice, I stored the Shared Access Signature values in two Project Parameters as SASFolder1 and SASFolder2.
Blob file URLs are store in package variables as OriginalBlobFileURL and MoveBlobFileURL

To move the file, I used a Script Task and Variables section of the Script Task Editor is as below

Click on the Edit Script to add the below code

string sasFolder1 = Dts.Variables["$Project::SASFolder1"].Value.ToString();
string sasFolder2 = Dts.Variables["$Project::SASFolder2"].Value.ToString();
string originalBlobFileURL = Dts.Variables["User::OriginalBlobFileURL"].Value.ToString();
string moveBlobFileURL = Dts.Variables["User::MoveBlobFileURL"].Value.ToString();

try
 {
        HttpClient client = new HttpClient();
        client.DefaultRequestHeaders.Add("x-ms-copy-source", originalBlobFileURL + sasFolder1);
        //copy file from Folder1 to Folder2
       HttpResponseMessage response = await client.PutAsync(moveBlobFileURL + sasFolder2, null);
       if (response.StatusCode == HttpStatusCode.Accepted || response.StatusCode == HttpStatusCode.Created)
         {
              client.DefaultRequestHeaders.Clear();
              // delete the original file in the Folder1
              HttpResponseMessage result = await client.DeleteAsync(originalBlobFileURL + sasFolder1);
              if (result.StatusCode == HttpStatusCode.Accepted || result.StatusCode == HttpStatusCode.Created)
               {
                        Dts.TaskResult = (int)ScriptResults.Success;
                        return;
               }
       }
       Dts.TaskResult = (int)ScriptResults.Failure;
}
catch (Exception ex)
{
      Dts.TaskResult = (int)ScriptResults.Failure;
}

Build the code and Save it.
Click Ok button in the Script Task Editor.
That is all...

Configuring SSIS Foreach Loop Container for Azure Blob Storage

Like we iterate through files in File System, we can iterate through files in Azure Blob Storage, using SSIS Foreach Loop Container.
If you consider about the Blob Storage structure, there will be Containers and inside those containers, you will find Folders.

Below Project Parameters are added to store those configurations information
BlobContainer - Stores the Blob Container name
BlobFolder - Stores the Folder name
ConnStr_AzureStorage - Stores the connection string value for the Azure Blob Storage
(Ex: DefaultEndpointsProtocol=https;AccountName=blobstorageaccountname;AccountKey=accountkey;EndpointSuffix=core.windows.net)

Addition to those, package variable is added to store the Blob File Name as BlobFileName.

To create a connection to the Azure Blob Storage, right click on the Connection Managers section in the Package and select New Connection

In the Add SSIS Connection Manager window, select AzureStorage type and click Add

Then you will get the Azure Storage Connection Manager Editor window.

In that select Use Azure Account and provide the relevant Storage account name and Account key values.
Then test the connection using Test Connection button.
If it is Successful, click on the OK button. Otherwise please check the provided Storage account name and Account key values.
To set the Connection String value using the ConnStr_AzureStorage Project Parameter, select the Azure Connection and go the Properties tab.
In that under Expressions section, add the ConnStr_AzureStorage project parameter value to the ConnectionString property.

Once it is done, to configure Foreach Loop Container, drag and drop Foreach Loop Container to the package Control Flow and double click on it.
In the Collection section, click on the down arrow under Enumerator, and select Foreach Azure Blob Enumerator

Then select the previously added Azure Connection for the Azure storage connection manager and to provide the relevant values for the Blob container name and Blob directory values, click on the ... in front of the Expressions.

Set the BlobContainer  and BlobPath property values using the BlobContainer and BlobFolder project parameters.

To assign the Blob File Name to the BlobFileName variable, go to the Variable Mappings section and set the variable.

Now you have configured the Foreach Loop Container for Azure Blob Storage and you can do the required operations inside the Foreach Loop Container by using the File Name available in the BlobFileName variable.