Azure Feature Pack for Integration Services (SSIS)

APPLIES TO: yesSQL Server, including on Linux yesAzure SQL Database yesAzure SQL Data Warehouse noParallel Data Warehouse

SQL Server Integration Services (SSIS) Feature Pack for Azure is an extension that provides the components listed on this page for SSIS to connect to Azure services, transfer data between Azure and on-premises data sources, and process data stored in Azure.

Download SSIS Feature Pack for Azure Download

The download pages also include information about prerequisites. Make sure you install SQL Server before you install the Azure Feature Pack on a server, or the components in the Feature Pack may not be available when you deploy packages to the SSIS Catalog database, SSISDB, on the server.

Components in the Feature Pack

Use TLS 1.2

The TLS version used by Azure Feature Pack follows system .NET Framework settings. To use TLS 1.2, add a REG_DWORD value named SchUseStrongCrypto with data 1 under the following two registry keys.

  1. HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\.NETFramework\v4.0.30319
  2. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v4.0.30319

Dependency on Java

Java is required to use ORC/Parquet file formats with Azure Data Lake Store/Flat File connectors.
Architecture (32/64-bit) of the Java build should match that of the SSIS runtime to use. The following Java builds have been tested.

Set Up Zulu's OpenJDK

  1. Download and extract the installation zip package.
  2. From the Command Prompt, run sysdm.cpl.
  3. On the Advanced tab, select Environment Variables.
  4. Under the System variables section, select New.
  5. Enter JAVA_HOME for the Variable name.
  6. Select Browse Directory, navigate to the extracted folder, and select the jre subfolder. Then select OK, and the Variable value is populated automatically.
  7. Select OK to close the New System Variable dialog box.
  8. Select OK to close the Environment Variables dialog box.
  9. Select OK to close the System Properties dialog box.

Set Up Zulu's OpenJDK on Azure-SSIS Integration Runtime

This should be done via custom setup interface for Azure-SSIS Integration Runtime. Suppose zulu8.33.0.1-jdk8.0.192-win_x64.zip is used. The blob container could be organized as follows.

main.cmd
install_openjdk.ps1
zulu8.33.0.1-jdk8.0.192-win_x64.zip

As the entry point, main.cmd triggers execution of the PowerShell script install_openjdk.ps1 which in turn extracts zulu8.33.0.1-jdk8.0.192-win_x64.zip and sets JAVA_HOME accordingly.

main.cmd

powershell.exe -file install_openjdk.ps1

install_openjdk.ps1

Expand-Archive zulu8.33.0.1-jdk8.0.192-win_x64.zip -DestinationPath C:\
[Environment]::SetEnvironmentVariable("JAVA_HOME", "C:\zulu8.33.0.1-jdk8.0.192-win_x64\jre", "Machine")

Set Up Oracle's Java SE Runtime Environment

  1. Download and run the exe installer.
  2. Follow the installer instructions to complete setup.

Scenario: Processing big data

Use Azure Connector to complete following big data processing work:

  1. Use the Azure Blob Upload Task to upload input data to Azure Blob Storage.

  2. Use the Azure HDInsight Create Cluster Task to create an Azure HDInsight cluster. This step is optional if you want to use your own cluster.

  3. Use the Azure HDInsight Hive Task or Azure HDInsight Pig Task to invoke a Pig or Hive job on the Azure HDInsight cluster.

  4. Use the Azure HDInsight Delete Cluster Task to delete the HDInsight Cluster after use if you have created an on-demand HDInsight cluster in step #2.

  5. Use the Azure HDInsight Blob Download Task to download the Pig/Hive output data from the Azure Blob Storage.

SSIS-AzureConnector-BigDataScenario

Scenario: Managing data in the cloud

Use the Azure Blob Destination in an SSIS package to write output data to Azure Blob Storage, or use the Azure Blob Source to read data from an Azure Blob Storage.

SSIS-AzureConnector-CloudArchive-1

SSIS-AzureConnector-CloudArchive-2

Use the Foreach Loop Container with the Azure Blob Enumerator to process data in multiple blob files.

SSIS-AzureConnector-CloudArchive-3