Update for SQL Server Integration Services Feature Pack for Azure with support to Azure Data Lake Store and Azure SQL Data Warehouse

This post has been republished via RSS; it originally appeared at: SQL Server Integration Services (SSIS) articles.

First published on MSDN on Dec 29, 2016
Hi All,

We are pleased to announce that an updated version of SQL Server Integration Services Feature Pack for Azure is now available for download. This release mainly has following improvements:

  1. Support for Azure Data Lake Store

  2. Support for Azure SQL Data Warehouse

Here are the download links for the supported versions:

SSIS 2012: https://www.microsoft.com/en-us/download/details.aspx?id=47367

SSIS 2014: https://www.microsoft.com/en-us/download/details.aspx?id=47366

SSIS 2016: https://www.microsoft.com/en-us/download/details.aspx?id=49492

Azure Data Lake Store Components

1.In order to support Azure Data Lake Store (ADLS), SSIS add below two components:

  • Azure Data Lake Store Source:

    • User can use ADLS Source component to read data from ADLS.

    • Support Text and Avro file format.

  • Azure Data Lake Store Destination:

    • User can use ADLS Destination component to write data into ADLS.

    • Support Text, Avro and Orc file format.

    • In order to use Orc format, user need to install JRE

2. ADLS components support two authentication options:

  • Azure AD User Identity

    • If the Azure Data Lake Store AAD user or the AAD tenant administrator didn't consent "SQL Server Integration Service(Azure Data Lake)" to access their Azure Data Lake Store data before, then either AAD user or AAD tenant administrator need consent SSIS application to access Azure Data Lake Store data. For more information about this consent experience, see Integrating applications with Azure Active Directory .

    • Multi-factor authentication and Microsoft account is NOT supported. Consider to use "Azure AD Service Identity" option if your user account need multi-factor authentication or your user account is a Microsoft account.

  • Azure AD Service Identity

3. The ADLS source editor dialog is as below:

For more information about how to use Azure Data Lake Store components, see Azure Data Lake Store Components .

Azure SQL Data Warehouse

There are multiple approaches to load local data to Azure SQL Data Warehouse (Azure SQL DW) in SSIS. The blog post Azure SQL Data Warehouse Loading Patterns and Strategies gives a fine description and comparison of different approaches. A key point made in the post is that the recommended and most efficient approach that fully exploits the massively parallel processing power of Azure SQL DW is by using PolyBase. That is, first load data to Azure Blob Storage, and then to Azure SQL DW from there using PolyBase. The second step is done by executing a T-SQL sequence on Azure SQL DW.

While conceptually straightforward, it’s not an easy job to implement this approach in SSIS before this release. You have to use an Azure Blob Upload Task, followed by an Execute SQL Task, and possibly followed by yet another task to clean-up the temporary files uploaded to Azure Blob Storage. You also have to put together the complicated T-SQL sequence yourself.

To address this issue, this new release introduces a new control flow task Azure SQL DW Upload Task to provide a one-stop solution to Azure SQL DW data uploading. It automates the complicated process with an integrated, easy-to-manage interface.

On the General page, you configure basic properties about source data, Azure Blob Storage, and Azure SQL DW. Either a new table name or an existing one is specified for the TableName property, making a create or insert scenario.

The Mappings page appears differently for create and insert scenarios. In a create scenario, configure which source columns are mapped and their corresponding names in the to-be-created destination table. In an insert scenario, configure the mapping relationships between source and destination columns.

On the Columns page, configure data type properties for each source column.

The T-SQL page shows the T-SQL sequence for loading data from Azure Blob Storage to Azure SQL DW using PolyBase. It will be automatically generated from configurations made on the other pages. Still, nothing is preventing you from manually editing the T-SQL to meet your particular needs by clicking the Edit button.

For more information about how to use Azure SQL DW Upload Task, see Azure SQL DW Upload Task .

Leave a Reply

Your email address will not be published. Required fields are marked *


This site uses Akismet to reduce spam. Learn how your comment data is processed.