Introduction
In this post we will see how to set Metadata key/value in Azure Blob Storage Task and Amazon Storage Task (S3 Task). We will also see how to set them dynamically at runtime (Use SSIS Expression).
Prerequisites
Before we perform the steps listed in this article, you will need to make sure the following prerequisites are met:- SSIS designer installed. Sometimes it is referred to as BIDS or SSDT (download it from the Microsoft site).
- Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
- Make sure ZappySys SSIS PowerPack is installed (download it, if you haven't already).
- (Optional step). Read this article, if you are planning to deploy packages to a server and schedule their execution later.
Set Amazon S3 Task Metadata (Key / Value Pairs)
Here is how you can set Metadata in Amazon S3 Task.
Setting Amazon S3 Metadata (Key/Value Pairs) in SSIS Amazon Storage Task
Set Azure Blob Storage Task Metadata (Key / Value Pairs)
Just like S3 Task you can use same UI in Azure blob storage task too.
Set Metadata Key/Value Dynamically (SSIS Expression)
So far you have set metadata using static value. What if you have to set Dynamically at runtime?
Basically Metadata is passed as XML Format in the task like below.
|
1 2 3 4 5 6 7 8 9 10 11 |
<?xml version="1.0" encoding="utf-8"?> <ArrayOfStringKeyValue xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <StringKeyValue> <Key>x-amz-key1</Key> <Value>AAAAAAA</Value> </StringKeyValue> <StringKeyValue> <Key>x-amz-key2</Key> <Value>BBBBBBB</Value> </StringKeyValue> </ArrayOfStringKeyValue> |
Whate we will do is take above format and make it dynamic using SSIS Expression.
Perform the following steps for that.
- Select your Amazon Storage Task or Azure Storage Task and right click > Select Properties.
- In the Properties Grid check Property name MetadataKeyValue. Copy XML Value from there (We will use it in expression)
- Now double click Amazon Storage Task or Azure Storage Task
- Click on Expression Button
- Select MetadataKeyValue property and for Value enter below expression… Use previously copied XML text and change Value part with your own SSIS variable. In below example we are passing Just one Metadata Key/Value pair ( Name=x-amz-meta-filename , Value=Coming from @[User::MyFileNameVariable] )
Note that we have escaped double quote with \" and replaced static value with <Value>" + @[User::MyFileNameVariable] + "</Value> .
1234567891011"<?xml version=\"1.0\" encoding=\"utf-8\"?>"+ "<ArrayOfStringKeyValue xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">"+ " <StringKeyValue>"+ " <Key>x-amz-meta-file-name</Key>"+ " <Value>" + @[User::MyFileNameVariable] + "</Value>"+ " </StringKeyValue>"+ " <StringKeyValue>"+ " <Key>x-amz-meta-something</Key>"+ " <Value>BBBBBBB</Value>"+ " </StringKeyValue>"+ "</ArrayOfStringKeyValue>"
Set Amazon S3 File Metadata Key/Value dynamically
- Click OK and that’s it.
