{"id":6107,"date":"2019-01-17T15:21:54","date_gmt":"2019-01-17T15:21:54","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=6107"},"modified":"2019-01-17T15:21:54","modified_gmt":"2019-01-17T15:21:54","slug":"setting-metadata-key-value-ssis-amazon-s3-azure-storage-task","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/setting-metadata-key-value-ssis-amazon-s3-azure-storage-task\/","title":{"rendered":"Setting Metadata key \/ value in SSIS Amazon S3 \/ Azure Storage Task"},"content":{"rendered":"

Introduction<\/h2>\n

In this post we will see how to set Metadata key\/value in Azure Blob Storage Task<\/a> and Amazon Storage Task (S3 Task)<\/a>. We will also see how to set them dynamically at runtime (Use SSIS Expression).<\/p>\n

Prerequisites<\/span><\/h2>\r\nBefore we perform the steps listed in this article, you will need to make sure the following prerequisites are met:\r\n
    \r\n \t
  1. SSIS<\/abbr> designer installed.\u00a0Sometimes it is referred to as BIDS<\/abbr> or SSDT<\/abbr> (download it from the Microsoft site<\/a>).<\/li>\r\n \t
  2. Basic knowledge of SSIS package\u00a0development using\u00a0Microsoft SQL Server Integration Services<\/em>.<\/li>\r\n \t
  3. Make sure\u00a0ZappySys SSIS PowerPack<\/a><\/span>\u00a0is installed (download it<\/a>, if you haven't already).<\/li>\r\n \t
  4. (Optional step<\/em>).<\/em>\u00a0Read this article<\/a>, if you are planning to deploy packages to a server and schedule their execution later.<\/li>\r\n<\/ol><\/div>\n

    Set Amazon S3 Task Metadata (Key \/ Value Pairs)<\/h2>\n

    Here is how you can set Metadata in Amazon S3 Task.<\/p>\n

    \"Setting

    Setting Amazon S3 Metadata (Key\/Value Pairs) in SSIS Amazon Storage Task<\/p><\/div>\n

    Set Azure Blob Storage Task Metadata (Key \/ Value Pairs)<\/h2>\n

    Just like S3 Task you can use same UI in Azure blob storage task too.<\/p>\n

    Set Metadata Key\/Value Dynamically (SSIS Expression)<\/h2>\n

    So far you have set metadata using static value. What if you have to set Dynamically at runtime?<\/p>\n

    Basically Metadata is passed as XML Format in the task like below.<\/p>\n

    <?xml version=\"1.0\" encoding=\"utf-8\"?>\r\n<ArrayOfStringKeyValue xmlns:xsi=\"http:\/\/www.w3.org\/2001\/XMLSchema-instance\" xmlns:xsd=\"http:\/\/www.w3.org\/2001\/XMLSchema\">\r\n\t<StringKeyValue>\r\n\t\t<Key>x-amz-key1<\/Key>\r\n\t\t<Value>AAAAAAA<\/Value>\r\n\t<\/StringKeyValue>\r\n\t<StringKeyValue>\r\n\t\t<Key>x-amz-key2<\/Key>\r\n\t\t<Value>BBBBBBB<\/Value>\r\n\t<\/StringKeyValue>\t\r\n<\/ArrayOfStringKeyValue><\/pre>\n
    Whate we will do is take above format and make it dynamic using SSIS Expression.<\/p>\n
    Perform the following steps for that.<\/p>\n
      \n
    1. Select your\u00a0Amazon Storage Task or Azure Storage Task and right click > Select Properties.<\/li>\n
    2. In the Properties Grid check Property name\u00a0MetadataKeyValue. Copy XML Value from there (We will use it in expression)<\/li>\n
    3. Now double click Amazon Storage Task or Azure Storage Task<\/li>\n
    4. Click on Expression Button<\/li>\n
    5. Select MetadataKeyValue property and for Value enter below expression… Use previously copied XML text\u00a0 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\u00a0 , Value=Coming from\u00a0@[User::MyFileNameVariable] )
      \nNote that we have escaped double quote with \\”<\/span>\u00a0 and replaced static value with <Value>” + @[User::MyFileNameVariable] + “<\/Value><\/span>\u00a0.<\/p>\n
      \"<?xml version=\\\"1.0\\\" encoding=\\\"utf-8\\\"?>\"\r\n+ \"<ArrayOfStringKeyValue xmlns:xsi=\\\"http:\/\/www.w3.org\/2001\/XMLSchema-instance\\\" xmlns:xsd=\\\"http:\/\/www.w3.org\/2001\/XMLSchema\\\">\"\r\n+ \"\t<StringKeyValue>\"\r\n+ \"\t\t<Key>x-amz-meta-file-name<\/Key>\"\r\n+ \"\t\t<Value>\" + @[User::MyFileNameVariable] + \"<\/Value>\"\r\n+ \"\t<\/StringKeyValue>\"\r\n+ \"\t<StringKeyValue>\"\r\n+ \"\t\t<Key>x-amz-meta-something<\/Key>\"\r\n+ \"\t\t<Value>BBBBBBB<\/Value>\"\r\n+ \"\t<\/StringKeyValue>\"\t\r\n+ \"<\/ArrayOfStringKeyValue>\"<\/pre>\n
      \"Set<\/a>
      Set Amazon S3 File Metadata Key\/Value dynamically<\/p><\/div><\/li>\n
    6. \u00a0Click OK and that’s it.<\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"
      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). Set Amazon S3 Task Metadata (Key \/ Value Pairs) Here is how you can set Metadata in Amazon […]<\/p>\n","protected":false},"author":1,"featured_media":6108,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[181,182],"tags":[29,183,35,12],"yoast_head":"\r\nSetting Metadata key \/ value in SSIS Amazon S3 \/ Azure Storage Task | ZappySys Blog<\/title>\r\n<meta name=\"description\" content=\"Setting Metadata key \/ value in SSIS Amazon S3 \/ Azure Storage Task - ZappySys Blog\" \/>\r\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\r\n<link rel=\"canonical\" href=\"https:\/\/zappysys.com\/blog\/setting-metadata-key-value-ssis-amazon-s3-azure-storage-task\/\" \/>\r\n<meta property=\"og:locale\" content=\"en_US\" \/>\r\n<meta property=\"og:type\" content=\"article\" \/>\r\n<meta property=\"og:title\" content=\"Setting Metadata key \/ value in SSIS Amazon S3 \/ Azure Storage Task | ZappySys Blog\" \/>\r\n<meta property=\"og:description\" content=\"Setting Metadata key \/ value in SSIS Amazon S3 \/ Azure Storage Task - ZappySys Blog\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/zappysys.com\/blog\/setting-metadata-key-value-ssis-amazon-s3-azure-storage-task\/\" \/>\r\n<meta property=\"og:site_name\" content=\"ZappySys Blog\" \/>\r\n<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/ZappySys\/\" \/>\r\n<meta property=\"article:published_time\" content=\"2019-01-17T15:21:54+00:00\" \/>\r\n<meta property=\"og:image\" content=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/01\/ssis-amazon-s3-storage-metadata-dynamic-expression.png\" \/>\r\n\t<meta property=\"og:image:width\" content=\"1080\" \/>\r\n\t<meta property=\"og:image:height\" content=\"892\" \/>\r\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\r\n<meta name=\"author\" content=\"ZappySys\" \/>\r\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\r\n<meta name=\"twitter:creator\" content=\"@https:\/\/twitter.com\/zappysys\/\" \/>\r\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"ZappySys\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/zappysys.com\/blog\/setting-metadata-key-value-ssis-amazon-s3-azure-storage-task\/\",\"url\":\"https:\/\/zappysys.com\/blog\/setting-metadata-key-value-ssis-amazon-s3-azure-storage-task\/\",\"name\":\"Setting Metadata key \/ value in SSIS Amazon S3 \/ Azure Storage Task | ZappySys Blog\",\"isPartOf\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/zappysys.com\/blog\/setting-metadata-key-value-ssis-amazon-s3-azure-storage-task\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/zappysys.com\/blog\/setting-metadata-key-value-ssis-amazon-s3-azure-storage-task\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/01\/ssis-amazon-s3-storage-metadata-dynamic-expression.png\",\"datePublished\":\"2019-01-17T15:21:54+00:00\",\"dateModified\":\"2019-01-17T15:21:54+00:00\",\"author\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82\"},\"description\":\"Setting Metadata key \/ value in SSIS Amazon S3 \/ Azure Storage Task - ZappySys Blog\",\"breadcrumb\":{\"@id\":\"https:\/\/zappysys.com\/blog\/setting-metadata-key-value-ssis-amazon-s3-azure-storage-task\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/zappysys.com\/blog\/setting-metadata-key-value-ssis-amazon-s3-azure-storage-task\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/setting-metadata-key-value-ssis-amazon-s3-azure-storage-task\/#primaryimage\",\"url\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/01\/ssis-amazon-s3-storage-metadata-dynamic-expression.png\",\"contentUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/01\/ssis-amazon-s3-storage-metadata-dynamic-expression.png\",\"width\":1080,\"height\":892,\"caption\":\"Set Amazon S3 File Metadata Key\/Value dynamically\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/zappysys.com\/blog\/setting-metadata-key-value-ssis-amazon-s3-azure-storage-task\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/zappysys.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Setting Metadata key \/ value in SSIS Amazon S3 \/ Azure Storage Task\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/zappysys.com\/blog\/#website\",\"url\":\"https:\/\/zappysys.com\/blog\/\",\"name\":\"ZappySys Blog\",\"description\":\"SSIS \/ ODBC Drivers \/ API Connectors for JSON, XML, Azure, Amazon AWS, Salesforce, MongoDB and more\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/zappysys.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82\",\"name\":\"ZappySys\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g\",\"caption\":\"ZappySys\"},\"sameAs\":[\"http:\/\/www.zappysys.com\/\",\"https:\/\/www.facebook.com\/ZappySys\/\",\"https:\/\/twitter.com\/https:\/\/twitter.com\/zappysys\/\"],\"url\":\"https:\/\/zappysys.com\/blog\/author\/admin\/\"}]}<\/script>\r\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Setting Metadata key \/ value in SSIS Amazon S3 \/ Azure Storage Task | ZappySys Blog","description":"Setting Metadata key \/ value in SSIS Amazon S3 \/ Azure Storage Task - ZappySys Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/zappysys.com\/blog\/setting-metadata-key-value-ssis-amazon-s3-azure-storage-task\/","og_locale":"en_US","og_type":"article","og_title":"Setting Metadata key \/ value in SSIS Amazon S3 \/ Azure Storage Task | ZappySys Blog","og_description":"Setting Metadata key \/ value in SSIS Amazon S3 \/ Azure Storage Task - ZappySys Blog","og_url":"https:\/\/zappysys.com\/blog\/setting-metadata-key-value-ssis-amazon-s3-azure-storage-task\/","og_site_name":"ZappySys Blog","article_author":"https:\/\/www.facebook.com\/ZappySys\/","article_published_time":"2019-01-17T15:21:54+00:00","og_image":[{"width":1080,"height":892,"url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/01\/ssis-amazon-s3-storage-metadata-dynamic-expression.png","type":"image\/png"}],"author":"ZappySys","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/twitter.com\/zappysys\/","twitter_misc":{"Written by":"ZappySys","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/zappysys.com\/blog\/setting-metadata-key-value-ssis-amazon-s3-azure-storage-task\/","url":"https:\/\/zappysys.com\/blog\/setting-metadata-key-value-ssis-amazon-s3-azure-storage-task\/","name":"Setting Metadata key \/ value in SSIS Amazon S3 \/ Azure Storage Task | ZappySys Blog","isPartOf":{"@id":"https:\/\/zappysys.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/zappysys.com\/blog\/setting-metadata-key-value-ssis-amazon-s3-azure-storage-task\/#primaryimage"},"image":{"@id":"https:\/\/zappysys.com\/blog\/setting-metadata-key-value-ssis-amazon-s3-azure-storage-task\/#primaryimage"},"thumbnailUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/01\/ssis-amazon-s3-storage-metadata-dynamic-expression.png","datePublished":"2019-01-17T15:21:54+00:00","dateModified":"2019-01-17T15:21:54+00:00","author":{"@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82"},"description":"Setting Metadata key \/ value in SSIS Amazon S3 \/ Azure Storage Task - ZappySys Blog","breadcrumb":{"@id":"https:\/\/zappysys.com\/blog\/setting-metadata-key-value-ssis-amazon-s3-azure-storage-task\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/zappysys.com\/blog\/setting-metadata-key-value-ssis-amazon-s3-azure-storage-task\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/setting-metadata-key-value-ssis-amazon-s3-azure-storage-task\/#primaryimage","url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/01\/ssis-amazon-s3-storage-metadata-dynamic-expression.png","contentUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/01\/ssis-amazon-s3-storage-metadata-dynamic-expression.png","width":1080,"height":892,"caption":"Set Amazon S3 File Metadata Key\/Value dynamically"},{"@type":"BreadcrumbList","@id":"https:\/\/zappysys.com\/blog\/setting-metadata-key-value-ssis-amazon-s3-azure-storage-task\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/zappysys.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Setting Metadata key \/ value in SSIS Amazon S3 \/ Azure Storage Task"}]},{"@type":"WebSite","@id":"https:\/\/zappysys.com\/blog\/#website","url":"https:\/\/zappysys.com\/blog\/","name":"ZappySys Blog","description":"SSIS \/ ODBC Drivers \/ API Connectors for JSON, XML, Azure, Amazon AWS, Salesforce, MongoDB and more","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/zappysys.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82","name":"ZappySys","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g","caption":"ZappySys"},"sameAs":["http:\/\/www.zappysys.com\/","https:\/\/www.facebook.com\/ZappySys\/","https:\/\/twitter.com\/https:\/\/twitter.com\/zappysys\/"],"url":"https:\/\/zappysys.com\/blog\/author\/admin\/"}]}},"_links":{"self":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/6107"}],"collection":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/comments?post=6107"}],"version-history":[{"count":1,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/6107\/revisions"}],"predecessor-version":[{"id":6109,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/6107\/revisions\/6109"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media\/6108"}],"wp:attachment":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media?parent=6107"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/categories?post=6107"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/tags?post=6107"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}