{"id":128,"date":"2015-10-27T19:55:36","date_gmt":"2015-10-27T19:55:36","guid":{"rendered":"http:\/\/zappysys.com\/blog\/?p=128"},"modified":"2016-12-22T06:04:34","modified_gmt":"2016-12-22T06:04:34","slug":"extract-unload-redshift-data-sql-server-using-ssis","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/extract-unload-redshift-data-sql-server-using-ssis\/","title":{"rendered":"Extract \/ Unload Redshift data into SQL Server using SSIS"},"content":{"rendered":"

Introduction<\/h2>\n

In our previous article we saw how to load data into Redshift using SSIS<\/a> or load data into Redshift using ZappyShell Redshift Command Line<\/a><\/p>\n

In this article we will walk through various steps to Extract\/UNLOAD Redshift Data into SQL Server<\/em> using Amazon S3 Storage Task<\/a> and ExecuteSQL Task for Amazon Redshift<\/a>. Below is the screenshot of actual SSIS Package to Extract Redshift Data and Load into SQL Server<\/em><\/p>\n

\"Extract\/Unload<\/a>

Extract\/Unload Redshift Data using SSIS and Load into SQL Server<\/p><\/div>\n

Requirements\u00a0for Extract Redshift Data using SSIS<\/h2>\n

Before you UNLOAD<\/a>\u00a0data from Redshift, you have to make sure few things.<\/p>\n

    \n
  1. Setup your Redshift cluster (Follow these instructions to setup redshift cluster<\/a>)<\/li>\n
  2. Load some sample data to Redshift (Red more here: How to load data to Redshift<\/a>)<\/li>\n
  3. Make sure you have correct connection settings to connect to Redshift cluster (Host name, Port, UserId, Password, DB name\u00a0etc). You can get host name from AWS Console.<\/li>\n
  4. Make sure you have Access to S3 Bucket where files will be dumped from Redshift. You will need AccessKey and SecretKey to fetch files from S3<\/li>\n<\/ol>\n

    Step-1:\u00a0Execute Redshift UNLOAD Command<\/h2>\n

    Very first step would be to unload redshift data as GZip file using ExecuteSQL Task for Amazon Redshift<\/a>
    \nBelow is SQL Command you can use to extract data from Redshift<\/em>. Notice how we used variable placeholders in SQL Command. These placeholders are replaced at runtime with actual value stored in specified variable.<\/p>\n

    unload ('select * from (select * from customerdata limit 1000)')\r\nto 's3:\/\/bw-rstest\/stage\/custdata'\r\ncredentials 'aws_access_key_id={{User::S3Accesskey}};aws_secret_access_key={{User::S3SecretKey}}'\r\nALLOWOVERWRITE<\/pre>\n
    Export as GZip files (Compressed files)<\/b><\/p>\n
    If you exporting data as compressed files\u00a0to save\u00a0data transfer cost then use GZIP option\u00a0as below.<\/p>\n
    NOTE:<\/strong> Make sure there are no spaces before and after AccessKey and SecretKey otherwise you may get error.<\/p>\n
    unload ('select * from (select * from customerdata limit 1000)')\r\nto 's3:\/\/bw-rstest\/stage\/custdata_file_'\r\ncredentials 'aws_access_key_id={{User::S3Accesskey}};aws_secret_access_key={{User::S3SecretKey}}'\r\nALLOWOVERWRITE\r\nGZIP<\/pre>\n
    Common Errors \/ Troubleshooting<\/h3>\n
    UNLOAD command issue with Region mismatch (S3 bucket vs Redshift Cluster)<\/strong><\/p>\n
    If your S3 bucket is in different region than Redshift cluster then above command may fail with “301 permanent redirect error<\/em>” in that case you have to change your S3 bucket region.\u00a0Region can be changed in AWS console (See S3 bucket properties and change location to match region with Redshift cluster region. Both regions must be same.<\/p>\n
    ERROR: XX000: S3ServiceException:The bucket you are attempting to access must be addressed using the specified endpoint. Please send all future requests to this endpoint.,Status 301,Error PermanentRedirect<\/p><\/blockquote>\n
    UNLOAD command issue with accesskey and secret key<\/strong><\/p>\n
    If you specify invalid accesskey or secretkey –or– you have misspelled keywords related to credentials — or — you have spaces before or after accesskey or secret key then you may get following error.<\/p>\n
    ERROR: XX000: Invalid credentials. Must be of the format: credentials ‘aws_iam_role=…’ or ‘aws_access_key_id=…;aws_secret_access_key=…[;token=…].<\/p><\/blockquote>\n
    Step-2: Download data files from Amazon S3 Bucket to local machine<\/h2>\n
    Once files are exported to S3 bucket we can download then to local machine using Amazon S3 Storage Task<\/a><\/p>\n
    Step-3: Un-compress downloaded files<\/h2>\n
    If you have exported Redshift data as compressed files (using GZIP option) then you can use ZappySys Zip File task<\/a> to un-compress multiple files.<\/p>\n
    Or you can write Script to un-compress those files (see below code). You can skip this step if files are not compressed (not used GZIP option in command).<\/p>\n
    Here is sample C# code<\/strong> to un-compress GZip<\/strong> files<\/p>\n
    public void Main()\r\n{\r\n\tSystem.IO.DirectoryInfo directorySelected = new System.IO.DirectoryInfo(@\"C:\\amazon\\archive\");\r\n\r\n\tforeach (System.IO.FileInfo fileToDecompress in directorySelected.GetFiles(\"custdata*_part_*\"))\r\n\t{\r\n\t\tDecompress(fileToDecompress);\r\n\t}\r\n\r\n\tDts.TaskResult = (int)ScriptResults.Success;\r\n}\r\nprivate static void Decompress(System.IO.FileInfo fileToDecompress)\r\n{\r\n\tusing (System.IO.FileStream originalFileStream = fileToDecompress.OpenRead())\r\n\t{\r\n\t\tstring currentFileName = fileToDecompress.FullName;\r\n\t\tstring newFileName = currentFileName.Remove(currentFileName.Length - fileToDecompress.Extension.Length);\r\n\r\n\t\tusing (System.IO.FileStream decompressedFileStream = System.IO.File.Create(newFileName))\r\n\t\t{\r\n\t\t\tusing (System.IO.Compression.GZipStream decompressionStream = new System.IO.Compression.GZipStream(originalFileStream, System.IO.Compression.CompressionMode.Decompress))\r\n\t\t\t{\r\n\t\t\t\tdecompressionStream.CopyTo(decompressedFileStream);\r\n\t\t\t\t\/\/Console.WriteLine(\"Decompressed: {0}\", fileToDecompress.Name);\r\n\t\t\t}\r\n\t\t}\r\n\t}\r\n}\r\n<\/pre>\n
     <\/p>\n
    Step-4: Loop through files using ForEachLoop Container<\/h2>\n
    Once files downloaded from S3 bucket we can now loop through files using SSIS ForEach Loop Task and load into SQL Server (One file in each iteration)<\/p>\n
    \"Loop<\/a>
    Loop through files downloaded from Amazon S3 (Exported using Redshift UNLOAD Command)<\/p><\/div>\n
    Step-5: Data Flow – Load Redshift Data Files to SQL Server<\/h2>\n
    Inside data flow you can use Flat File source and OLEDB Destination for SQL Server. Just map correct File columns to SQL Server fields and you should be good. If needed convert Unicode\/Non-unicode columns using Data Conversion Transform (This is not needed if source is DT_STR and target also DT_STR.. or source is DT_WSTR and target is DT_WSTR i.e. Unicode).<\/p>\n
    Downloads<\/h2>\n
    To download above SSIS Package click on the below links. In order to test below package you first have to download SSIS PowerPack<\/a>
    \n    Download Demo SSIS Package – SSIS 2008<\/a>
    \n    Download Demo SSIS Package – SSIS 2012\/2014<\/a><\/p>\n
    Conclusion<\/h2>\n
    amazon Redshift is great way to start your data warehouse projects with very minimum investment in a very simple pay as you go model but loading or unloading data from redshift can be challenging task. Using\u00a0SSIS PowerPack<\/a>\u00a0you can perform Redshift data load or unload in few clicks.<\/p>\n","protected":false},"excerpt":{"rendered":"
    Introduction In our previous article we saw how to load data into Redshift using SSIS or load data into Redshift using ZappyShell Redshift Command Line In this article we will walk through various steps to Extract\/UNLOAD Redshift Data into SQL Server using Amazon S3 Storage Task and ExecuteSQL Task for Amazon Redshift. Below is the […]<\/p>\n","protected":false},"author":1,"featured_media":164,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[43,41,44],"tags":[28,29,34,33,32,31,30,35,12,4],"yoast_head":"\r\nExtract \/ Unload Redshift data into SQL Server using SSIS | ZappySys Blog<\/title>\r\n<meta name=\"description\" content=\"Learn how to extract \/ unload Redshift data using SSIS. Steps for Redshift Data Extract : Redshift UNLOAD > Download compressed S3 file > Load to SQL Server\" \/>\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\/extract-unload-redshift-data-sql-server-using-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=\"Extract \/ Unload Redshift data into SQL Server using SSIS | ZappySys Blog\" \/>\r\n<meta property=\"og:description\" content=\"Learn how to extract \/ unload Redshift data using SSIS. Steps for Redshift Data Extract : Redshift UNLOAD > Download compressed S3 file > Load to SQL Server\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/zappysys.com\/blog\/extract-unload-redshift-data-sql-server-using-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=\"2015-10-27T19:55:36+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2016-12-22T06:04:34+00:00\" \/>\r\n<meta property=\"og:image\" content=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/10\/extract-unload-redshift-data-load-to-sql-server-ssis.png\" \/>\r\n\t<meta property=\"og:image:width\" content=\"654\" \/>\r\n\t<meta property=\"og:image:height\" content=\"565\" \/>\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=\"5 minutes\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/zappysys.com\/blog\/extract-unload-redshift-data-sql-server-using-ssis\/\",\"url\":\"https:\/\/zappysys.com\/blog\/extract-unload-redshift-data-sql-server-using-ssis\/\",\"name\":\"Extract \/ Unload Redshift data into SQL Server using SSIS | ZappySys Blog\",\"isPartOf\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/zappysys.com\/blog\/extract-unload-redshift-data-sql-server-using-ssis\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/zappysys.com\/blog\/extract-unload-redshift-data-sql-server-using-ssis\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/10\/extract-unload-redshift-data-load-to-sql-server-ssis.png\",\"datePublished\":\"2015-10-27T19:55:36+00:00\",\"dateModified\":\"2016-12-22T06:04:34+00:00\",\"author\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82\"},\"description\":\"Learn how to extract \/ unload Redshift data using SSIS. Steps for Redshift Data Extract : Redshift UNLOAD > Download compressed S3 file > Load to SQL Server\",\"breadcrumb\":{\"@id\":\"https:\/\/zappysys.com\/blog\/extract-unload-redshift-data-sql-server-using-ssis\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/zappysys.com\/blog\/extract-unload-redshift-data-sql-server-using-ssis\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/extract-unload-redshift-data-sql-server-using-ssis\/#primaryimage\",\"url\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/10\/extract-unload-redshift-data-load-to-sql-server-ssis.png\",\"contentUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/10\/extract-unload-redshift-data-load-to-sql-server-ssis.png\",\"width\":654,\"height\":565,\"caption\":\"Extract\/Unload Redshift Data using SSIS and Load into SQL Server\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/zappysys.com\/blog\/extract-unload-redshift-data-sql-server-using-ssis\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/zappysys.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Extract \/ Unload Redshift data into SQL Server using 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":"Extract \/ Unload Redshift data into SQL Server using SSIS | ZappySys Blog","description":"Learn how to extract \/ unload Redshift data using SSIS. Steps for Redshift Data Extract : Redshift UNLOAD > Download compressed S3 file > Load to SQL Server","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\/extract-unload-redshift-data-sql-server-using-ssis\/","og_locale":"en_US","og_type":"article","og_title":"Extract \/ Unload Redshift data into SQL Server using SSIS | ZappySys Blog","og_description":"Learn how to extract \/ unload Redshift data using SSIS. Steps for Redshift Data Extract : Redshift UNLOAD > Download compressed S3 file > Load to SQL Server","og_url":"https:\/\/zappysys.com\/blog\/extract-unload-redshift-data-sql-server-using-ssis\/","og_site_name":"ZappySys Blog","article_author":"https:\/\/www.facebook.com\/ZappySys\/","article_published_time":"2015-10-27T19:55:36+00:00","article_modified_time":"2016-12-22T06:04:34+00:00","og_image":[{"width":654,"height":565,"url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/10\/extract-unload-redshift-data-load-to-sql-server-ssis.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":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/zappysys.com\/blog\/extract-unload-redshift-data-sql-server-using-ssis\/","url":"https:\/\/zappysys.com\/blog\/extract-unload-redshift-data-sql-server-using-ssis\/","name":"Extract \/ Unload Redshift data into SQL Server using SSIS | ZappySys Blog","isPartOf":{"@id":"https:\/\/zappysys.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/zappysys.com\/blog\/extract-unload-redshift-data-sql-server-using-ssis\/#primaryimage"},"image":{"@id":"https:\/\/zappysys.com\/blog\/extract-unload-redshift-data-sql-server-using-ssis\/#primaryimage"},"thumbnailUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/10\/extract-unload-redshift-data-load-to-sql-server-ssis.png","datePublished":"2015-10-27T19:55:36+00:00","dateModified":"2016-12-22T06:04:34+00:00","author":{"@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82"},"description":"Learn how to extract \/ unload Redshift data using SSIS. Steps for Redshift Data Extract : Redshift UNLOAD > Download compressed S3 file > Load to SQL Server","breadcrumb":{"@id":"https:\/\/zappysys.com\/blog\/extract-unload-redshift-data-sql-server-using-ssis\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/zappysys.com\/blog\/extract-unload-redshift-data-sql-server-using-ssis\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/extract-unload-redshift-data-sql-server-using-ssis\/#primaryimage","url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/10\/extract-unload-redshift-data-load-to-sql-server-ssis.png","contentUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/10\/extract-unload-redshift-data-load-to-sql-server-ssis.png","width":654,"height":565,"caption":"Extract\/Unload Redshift Data using SSIS and Load into SQL Server"},{"@type":"BreadcrumbList","@id":"https:\/\/zappysys.com\/blog\/extract-unload-redshift-data-sql-server-using-ssis\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/zappysys.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Extract \/ Unload Redshift data into SQL Server using 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\/128"}],"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=128"}],"version-history":[{"count":3,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/128\/revisions"}],"predecessor-version":[{"id":918,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/128\/revisions\/918"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media\/164"}],"wp:attachment":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media?parent=128"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/categories?post=128"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/tags?post=128"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}