{"id":6255,"date":"2019-01-30T00:38:10","date_gmt":"2019-01-30T00:38:10","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=6255"},"modified":"2019-01-30T16:31:24","modified_gmt":"2019-01-30T16:31:24","slug":"convert-varbinary-base64-ssis","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/convert-varbinary-base64-ssis\/","title":{"rendered":"How to convert Varbinary to Base64 in SSIS"},"content":{"rendered":"

Introduction<\/h2>\n

In this post we will show you how to convert varbinary to Base64 in SSIS. We will use FREE Tasks<\/a> provided by ZappySys.<\/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

    Preparing Sample Data<\/h2>\n

    First let’s create a sample table with some Varbinary datatype. Run following command in SSMS to create a sample table with one sample row.<\/p>\n

    create table binarydata(\r\n data varbinary(max)\r\n)\r\ngo\r\ninsert into binarydata(data )\r\nvalues(0x5468697320697320612074657374) \/*binary of \"This is a test\"*\/<\/pre>\n
     <\/p>\n
    Method 1 – Using Template Transform<\/h2>\n
    Let’s use Template Transform<\/a>\u00a0to convert Varbinary data to Base64 format. When you use columns with binary data in Template transform it automatically converts to Base64 by default.<\/p>\n
      \n
    1. Go to Data flow<\/li>\n
    2. Drag your OLEDB Source and configure it to outputs SQL Data with Varbinary column\u00a0 (e.g. select data from dbo.binarydata).<\/li>\n
    3. Now drag ZS Template Transform<\/strong> from SSIS Toolbox<\/li>\n
    4. Connect OLEDB Source to Template Transform<\/li>\n
    5. Double click Template Transform\u00a0 to configure. Now you can click Insert Placeholders<\/strong> > Columns<\/strong> > Select your Upstream column<\/strong> name (which has Varbinary data)<\/li>\n
    6. You will see Template text as\u00a0 <%MyVarBinaryColumn%><\/li>\n
    7. Click OK to Save Template Transform<\/li>\n
    8. Now connect Template transform to any Destination e.g. OLEDB Destination.\u00a0 You can Map\u00a0TemplateOutput<\/strong> column to target column. This will contain Base64 representation of Varbinary data. For example purpose we used ZS Trash destination (Check save to file)<\/li>\n<\/ol>\n
       <\/p>\n
      \"Convert<\/a>
      Convert Varbinary to BASE64 using SSIS Template Transform<\/p><\/div>\n
      Save Base64 string to Variable<\/h3>\n
      In previous step we saw how Template Transform can convert binary data (i.e. Varbinary datatype) to BASE64 string. Now let’s look at how to save this value to Variable using Set Variable Transform<\/a>.<\/p>\n
      \"Using<\/a>
      Using SSIS Set Variable Transform in Data flow (Capture binary data as base64 example)<\/p><\/div>\n
      Method 2 –\u00a0Using Trash Destination<\/h2>\n
      Another way to dump Binary data to CSV file is use ZS Trans Destination. See previous screenshot. If you map OLEDB Source directly to Trash destination without Template Transform then you will see something like below.<\/p>\n
      data\r\nVGhpcyBpcyBhIHRlc3Q=<\/pre>\n
       <\/p>\n
       <\/p>\n","protected":false},"excerpt":{"rendered":"
      Introduction In this post we will show you how to convert varbinary to Base64 in SSIS. We will use FREE Tasks provided by ZappySys. Preparing Sample Data First let’s create a sample table with some Varbinary datatype. Run following command in SSMS to create a sample table with one sample row. create table binarydata( data […]<\/p>\n","protected":false},"author":1,"featured_media":6256,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[441,267,440],"tags":[147,12],"yoast_head":"\r\nHow to convert Varbinary to Base64 in SSIS | ZappySys Blog<\/title>\r\n<meta name=\"description\" content=\"How to convert Varbinary to Base64 in SSIS - 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\/convert-varbinary-base64-ssis\/\" \/>\r\n<meta property=\"og:locale\" content=\"en_US\" \/>\r\n<meta property=\"og:type\" content=\"article\" \/>\r\n<meta property=\"og:title\" content=\"How to convert Varbinary to Base64 in SSIS | ZappySys Blog\" \/>\r\n<meta property=\"og:description\" content=\"How to convert Varbinary to Base64 in SSIS - ZappySys Blog\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/zappysys.com\/blog\/convert-varbinary-base64-ssis\/\" \/>\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-30T00:38:10+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2019-01-30T16:31:24+00:00\" \/>\r\n<meta property=\"og:image\" content=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/01\/ssis-convert-varbinary-to-base64.png\" \/>\r\n\t<meta property=\"og:image:width\" content=\"634\" \/>\r\n\t<meta property=\"og:image:height\" content=\"488\" \/>\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\/convert-varbinary-base64-ssis\/\",\"url\":\"https:\/\/zappysys.com\/blog\/convert-varbinary-base64-ssis\/\",\"name\":\"How to convert Varbinary to Base64 in SSIS | ZappySys Blog\",\"isPartOf\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/zappysys.com\/blog\/convert-varbinary-base64-ssis\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/zappysys.com\/blog\/convert-varbinary-base64-ssis\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/01\/ssis-convert-varbinary-to-base64.png\",\"datePublished\":\"2019-01-30T00:38:10+00:00\",\"dateModified\":\"2019-01-30T16:31:24+00:00\",\"author\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82\"},\"description\":\"How to convert Varbinary to Base64 in SSIS - ZappySys Blog\",\"breadcrumb\":{\"@id\":\"https:\/\/zappysys.com\/blog\/convert-varbinary-base64-ssis\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/zappysys.com\/blog\/convert-varbinary-base64-ssis\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/convert-varbinary-base64-ssis\/#primaryimage\",\"url\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/01\/ssis-convert-varbinary-to-base64.png\",\"contentUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/01\/ssis-convert-varbinary-to-base64.png\",\"width\":634,\"height\":488,\"caption\":\"Convert Varbinary to BASE64 using SSIS Template Transform\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/zappysys.com\/blog\/convert-varbinary-base64-ssis\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/zappysys.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to convert Varbinary to Base64 in SSIS\"}]},{\"@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":"How to convert Varbinary to Base64 in SSIS | ZappySys Blog","description":"How to convert Varbinary to Base64 in SSIS - 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\/convert-varbinary-base64-ssis\/","og_locale":"en_US","og_type":"article","og_title":"How to convert Varbinary to Base64 in SSIS | ZappySys Blog","og_description":"How to convert Varbinary to Base64 in SSIS - ZappySys Blog","og_url":"https:\/\/zappysys.com\/blog\/convert-varbinary-base64-ssis\/","og_site_name":"ZappySys Blog","article_author":"https:\/\/www.facebook.com\/ZappySys\/","article_published_time":"2019-01-30T00:38:10+00:00","article_modified_time":"2019-01-30T16:31:24+00:00","og_image":[{"width":634,"height":488,"url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/01\/ssis-convert-varbinary-to-base64.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\/convert-varbinary-base64-ssis\/","url":"https:\/\/zappysys.com\/blog\/convert-varbinary-base64-ssis\/","name":"How to convert Varbinary to Base64 in SSIS | ZappySys Blog","isPartOf":{"@id":"https:\/\/zappysys.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/zappysys.com\/blog\/convert-varbinary-base64-ssis\/#primaryimage"},"image":{"@id":"https:\/\/zappysys.com\/blog\/convert-varbinary-base64-ssis\/#primaryimage"},"thumbnailUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/01\/ssis-convert-varbinary-to-base64.png","datePublished":"2019-01-30T00:38:10+00:00","dateModified":"2019-01-30T16:31:24+00:00","author":{"@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82"},"description":"How to convert Varbinary to Base64 in SSIS - ZappySys Blog","breadcrumb":{"@id":"https:\/\/zappysys.com\/blog\/convert-varbinary-base64-ssis\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/zappysys.com\/blog\/convert-varbinary-base64-ssis\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/convert-varbinary-base64-ssis\/#primaryimage","url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/01\/ssis-convert-varbinary-to-base64.png","contentUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/01\/ssis-convert-varbinary-to-base64.png","width":634,"height":488,"caption":"Convert Varbinary to BASE64 using SSIS Template Transform"},{"@type":"BreadcrumbList","@id":"https:\/\/zappysys.com\/blog\/convert-varbinary-base64-ssis\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/zappysys.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How to convert Varbinary to Base64 in SSIS"}]},{"@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\/6255"}],"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=6255"}],"version-history":[{"count":4,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/6255\/revisions"}],"predecessor-version":[{"id":6274,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/6255\/revisions\/6274"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media\/6256"}],"wp:attachment":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media?parent=6255"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/categories?post=6255"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/tags?post=6255"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}