Search

Saturday 18 February 2017

ZappySys SSIS Tasks - Importing data from SQL SERVER into Redshift


Recently, I've been using AWS (Amazon Web Services), primarily using the Redshift database as the platform for a Data Warehouse.
One of the challenges has been how to automate the data load from SQL SERVER, to Redshift.
There's various best practices - one of those is to store flatfile data in S3, prior to ingesting this into Redshift via the Redshift COPY command.
S3, or AWS S3 is a "simple storage service" offered by Amazon - the data is stored in "the cloud".
AWS offers AWS CLI (command line interface) to help achieve this task.
A rough guide would be to perform the following tasks
  1. Using SSIS (SQL SERVER INTEGRATION SERVICES) to read data from SQL SERVER, and create flatfiles.
  2. Using AWS CLI, push this data into an S3 bucket.
  3. Within Redshift - issue the COPY command, to pull the data from S3 into a Redshift table
  4. Optionally, issue another S3 command and move the files into an "Archive" folder - for better house keeping.
To achieve these 4 steps could be time consuming to automate.

This is where ZappySys comes in to help streamline this process, and cut out hours of development.
"ZappySys is a USA based software development company. 
We specialize in Data integration, Business Intelligence, 
Cloud computing and .Net Application Development."

In a nut-shell - ZappySys have developed SSIS task addon's that perform many different tasks - the one that's important for this import into Redshift is the Redshift Data Transfer task.
SSIS AWS Redshift Connector
This is equivalent to the SSIS Data Flow Task (DFT), but makes the whole process of connecting to the AWS account, Redshift cluster / database, using S3 as a flatfile staging environment a piece of cake.


SSIS Amazon AWS Redshift Data Transfer Task - Load from local file


Zappy even have recorded a straight-forward video on how to use this task to speed up the implementation.


Here's some more details (from http://zappysys.com/ to help summaries what they have to offer)

Why SSIS PowerPack?
  • Coding free, drag and drop high performance suite of Custom SSIS Components and SSIS Tasks
  • Used by many companies around the globe
  • Compatible with SQL Server 2005,2008, 2008 R2, 2012, 2014 and 2016
  • Support for 32bit and 64bit Server/Desktop OS
  • Easy to use, Familiar looks and feel and fully integrated in BIDS/SSDT
  • No license needed if you are developing/testing in BIDS/SSDT
  • Rapid update cycle and prompt support (See what’s new). We will help you over phone, web meetings and via email.
  • 30 days money back guarantee if not satisfied for whatever reason.

SSIS Integration Packs

SSIS Integration Pack offers cost effective way to accomplish your specific integration need. Click on the following link to find out more about each integration pack.

SSIS Tasks\Components by Category