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...







No comments:

Post a Comment

How to run UPDATE/INSERT/DELETE Statements on Azure SQL Database in Microsoft Fabric Notebook...

You can run UPDATE/INSERT/DELETE Statements on Azure SQL Database in Microsoft Fabric Notebook using Python SQL Driver - pyodbc.  For the Fa...