Creating PDW Connection in SSIS

•The SQL Server PDW Destination is an SSIS component that lets you load data into SQL Server PDW by using an SSIS .dtsx package,In the package workflow for SQL Server PDW, you can load and merge data from multiple sources and load data to multiple destinations,The loads occur in parallel, both within a package and among multiple packages running concurrently.

•PDW SSIS Destination adapter x64/x86

•SQL Server 2008 R2 – Visual Studio 2010

•SQL Server 2012 - Visual Studio 2012

•Development environment – SQL Server Data Tools

•SSDT 2010

•SSDT 2012

•SQL Server SSIS includes:

•SQL Server Parallel Data Warehouse Connection Manager

•SQL Server Parallel Data Warehouse Destination

•Similar to DWloader, SSIS leverages DMS for parallel load operations.

•SSIS can run on the Loading Server or on any other external Server

•SSIS Destination Adapter for SQL Server 2012 transfers data buffers to PDW on multiple parallel sessions

•Requirement for parallel data buffers transfer

•SQL Server 2012 plus Cumulative Update Package 5, or later.

•SQL Server 2012 Service Pack 1 plus Cumulative Update Package 2, or later.

•If the Cumulative Update Packages are not applied,
SSIS 2012 will transfer data to PDW serially

Start Visual Studio, and create a new SSIS project.

 •Microsoft .NET Framework 3.5 Service Pack 1

•PDW SSIS Destination adapter

•Install both x86 and x64 MSI

   packages

•SQL Server Data Tools 2010/2012

•SSIS 2008 R2 or SSIS 2012

 •SSIS Destination Adapter for SQL Server 2012 transfers data buffers to PDW on multiple parallel sessions

•Requirement for parallel data buffers transfer

•SQL Server 2012 plus Cumulative Update Package 5, or later.

•SQL Server 2012 Service Pack 1 plus Cumulative Update Package 2, or later.

•If the Cumulative Update Packages are not applied,
SSIS 2012 will transfer data to PDW serially

Add a Destination Connector

Click the Toolbox, and drag a SQL Server PDW Destination onto the data flow design surface.

•Double-click and select Create new connection…

 •Complete the configuration information

•Click Test connection, and then OK

•The PDW Data Flow Destination is created, along with the associated PDW connection manager

•When using SSIS to load data from a data source to a SQL Server PDW database:

•Data is first mapped from the source data to SSIS data types

•This allows data from multiple data sources to map to a common set of data types

•Then the data is mapped from SSIS to SQL Server PDW data types