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.
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.
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
property, making a create or insert scenario.
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.
page, configure data type properties for each source column.
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