Run Any SQL Anywhere in 3 Easy Steps with SSIS in Azure Data Factory

After releasing many features that unblock and accelerate the migration of SQL Server Integration Services (SSIS) packages into Azure Data Factory (ADF), see https://techcommunity.microsoft.com/t5/sql-server-integration-services/blast-to-the-future-accelerating-legacy-ssis-migrations-into/ba-p/1792821, we’d like to show you how to quickly extend ADF capabilities by leveraging these SSIS features, all without designing/deploying your own package.

 

Instead, we’ll provide a sample package with parameterized properties that you can simply download, drag & drop into an Execute SSIS Package activity in ADF pipeline and run it on your self-hosted/SSIS integration runtimes (SHIR/SSIS IR) with run-time parameter values assigned for your specific scenario.

 

This article illustrates 3 easy steps to run any SQL statements/script anywhere using SSIS in ADF: (1) Prepare your SHIR/SSIS IR; (2) Prepare an Execute SSIS Package activity in ADF pipeline; and (3) Run the Execute SSIS Package activity on your SHIR/SSIS IR.

 

1) Prepare your SHIR/SSIS IR

Our sample package will allow you to run any SQL statements/script on premises and in the cloud.  To access any SQL Server or other SQL-based relational database management system (RDBMS) on premises, your SSIS IR will need to join a virtual network (VNet) connected to your on-premises network or use an SHIR as a proxy.  For this purpose, we’ll use the proxy method that’s relatively easier/quicker to configure than the VNet method.

 

First, to prepare an SHIR as a proxy for your SSIS IR, see https://docs.microsoft.com/azure/data-factory/self-hosted-integration-runtime-proxy-ssis.

 

Next, to quickly prepare your SSIS IR for this purpose, you can unselect all checkboxes on the Deployment settings page of Integration runtime setup pane and only select the checkbox to configure an SHIR as a proxy on the Advanced settings page, see https://docs.microsoft.com/azure/data-factory/tutorial-deploy-ssis-packages-azure.

 

 

Since your SSIS IR isn’t joining a VNet, it should be running within 5 minutes.

 

2) Prepare an Execute SSIS Package activity in ADF pipeline

Once your SHIR and SSIS IR are running, you can go to the Integration runtimes page in ADF Manage hub and select the Execute SSIS package button on the right side of your SSIS IR name.

 

 

This will auto-create an ADF pipeline with Execute SSIS Package activity already in it. 

 

We’ve created a sample package (ExecuteSQL.dtsx) on SQL Server Data Tools (SSDT) containing 3 components named after their actual type/function: Execute SQL Task to run any SQL statements/script, OLEDB Connection Manager to access any SQL Server or other SQL-based RDBMS, and File Connection Manager to access any SQL script stored in file system.  We’ve also parameterized the following package properties for you to assign your run-time values:

 

-- ConnectionString: Enter the usual connectivity info for OLEDB Connection Manager, e.g.

    -- To use Windows Authentication:

        -- Data Source=[.|YourSQLServerName];Initial Catalog=YourDBName;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;

    -- To use SQL Authentication:

        -- Data Source=[.|YourSQLServerName|YourAzureSQLServerEndpoint];User ID=YourUserID;Password=YourPassword;Initial Catalog=YourDBName;Provider=SQLNCLI11.1;Persist Security Info=True;Auto Translate=False;

    -- To use Azure Active Directory (AAD) Authentication with ADF managed identity:

        -- Data Source=YourAzureSQLServerEndpoint;User ID=;Initial Catalog=YourDBName;Provider=MSOLEDBSQL;Integrated Security=SSPI;Auto Translate=False;

 

-- ConnectUsingManagedIdentity: Enter True or False to enable or disable AAD Authentication with ADF managed identity on your OLEDB Connection Manager, respectively.

 

-- ExecuteOnProxy: Enter True or False to run Execute SQL Task on your SHIR or SSIS IR, respectively.

 

-- FilePath: Enter the local/UNC path of your SQL script stored in file system.

 

-- SQLStatementSource: Enter your SQL statements or File Connection Manager depending on what you assigned to the SQLStatementSourceType parameter.

 

-- SQLStatementSourceType: Enter 1 or 2 to run your SQL statements assigned to the SQLStatementSource parameter or your SQL script file referenced by the FilePath parameter, respectively.

 

 

You can download our sample package from https://ssisazurefileshare.blob.core.windows.net/blogs/ExecuteSQL.dtsx and then drag & drop it as an embedded package on the Settings tab of your Execute SSIS Package activity in ADF pipeline.

 

 

3) Run the Execute SSIS Package activity on your SHIR/SSIS IR

Once you embed our sample package in your Execute SSIS Package activity in ADF pipeline, you can run it on your SHIR/SSIS IR with run-time parameter values assigned for your specific scenario.  Here are some examples:

 

Example 1) Run any SQL statements on your SQL Server on premises

To run any SQL statements on your SQL Server on premises, complete the following steps:

 

a) Configure the Windows authentication feature on the Settings tab of your Execute SSIS Package activity to connect to your SQL Server on premises with Azure Key Vault (AKV) to store your sensitive data.

 

 

b) Add the following parameters on the SSIS parameters tab to assign their run-time values:

 

    -- ConnectionString: Use Windows authentication, e.g.

Data Source=.;Initial Catalog=YourDBName;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;

    -- ConnectUsingManagedIdentity: False

    -- ExecuteOnProxy: True

    -- SQLStatementSource: Enter any SQL statements, e.g.

CREATE TABLE [Customer] (

[Firstname] nvarchar(50),

[Lastname] nvarchar(50),

[Street] nvarchar(50),

[Postalcode] nvarchar(50),

[City] nvarchar(50),

[Country] nvarchar(50))

    -- SQLStatementSourceType: 1

 

 

Example 2) Run any SQL script file on your SQL Server on premises

To run any SQL script file on your SQL Server on premises, complete the following steps:

 

a) If you store your SQL script file on the same machine as your SQL Server/SHIR:

 

    1) Configure the Windows authentication feature on the Settings tab of your Execute SSIS Package activity to connect to your SQL Server on premises with AKV to store your sensitive data – See Example 1 above.

 

    2) Add the following parameters on the SSIS parameters tab to assign their run-time values:

 

        -- ConnectionString: Use Windows authentication, e.g.

Data Source=.;Initial Catalog=YourDBName;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;

        -- ConnectUsingManagedIdentity: False

        -- ExecuteOnProxy: True

        -- FilePath: Enter the local path of your SQL script file, e.g.

C:\Users\YourUsername\Downloads\SQLScript.sql.

        -- SQLStatementSource: File Connection Manager

        -- SQLStatementSourceType: 2

 

b) If you store your SQL script file in Azure Files:

    1) Configure the Windows authentication feature on the Settings tab of your Execute SSIS Package activity to connect to Azure Files with AKV to store your sensitive data.

 

 

    2) Add the following parameters on the SSIS parameters tab to assign their run-time values:

 

        -- ConnectionString: Use SQL authentication, e.g.

Data Source=.;User ID=YourUserID;Password=YourPassword;Initial Catalog=YourDBName;Provider=SQLNCLI11.1;Persist Security Info=True;Auto Translate=False;

You can also configure AKV to store your sensitive data.

        -- ConnectUsingManagedIdentity: False

        -- ExecuteOnProxy: True

        -- FilePath: Enter the UNC path of your SQL script file, e.g.

//YourAzureStorageAccountName.file.core.windows.net/YourFileShareName/SQLScript.sql

        -- SQLStatementSource: File Connection Manager

        -- SQLStatementSourceType: 2

 

 

Example 3) Run any package stored in SSISDB hosted by your SQL Server on premises

To run any package stored in SSISDB hosted by your SQL Server on premises, complete the following steps:

 

a) Configure the Windows authentication feature on the Settings tab of your Execute SSIS Package activity to connect to your SQL Server on premises with AKV to store your sensitive data – See Example 1 above.

 

b) Add the following parameters on the SSIS parameters tab to assign their run-time values:

 

    -- ConnectionString: Use Windows authentication, e.g.

Data Source=.;Initial Catalog=SSISDB;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;

    -- ConnectUsingManagedIdentity: False

    -- ExecuteOnProxy: True

    -- SQLStatementSource:

DECLARE @return_value INT, @exe_id BIGINT, @err_msg NVARCHAR(150)

 

EXEC @return_value=[SSISDB].[catalog].[create_execution] @folder_name=N'YourFolderName', @project_name=N'YourProjectName', @package_name=N'YourPackageName.dtsx', @use32bitruntime=0, @execution_id=@exe_id OUTPUT

 

EXEC [SSISDB].[catalog].[set_execution_parameter_value] @exe_id, @object_type=50, @parameter_name=N'SYNCHRONIZED', @parameter_value=1

 

EXEC [SSISDB].[catalog].[start_execution] @execution_id=@exe_id, @retry_count=0

 

IF(SELECT [status] FROM [SSISDB].[catalog].[executions] WHERE execution_id=@exe_id)<>7

BEGIN

SET @err_msg=N'Your package execution did not succeed for execution ID: ' + CAST(@exe_id AS NVARCHAR(20))

RAISERROR(@err_msg,15,1)

END

    -- SQLStatementSourceType: 1

 

 

Example 4) Clean up package execution logs outside the configured retention window for SSISDB hosted by your Azure SQL Database server

To clean up package execution logs outside the configured retention window for SSISDB hosted by your Azure SQL Database server, complete the following steps:

 

a) Add the following parameters on the SSIS parameters tab of your Execute SSIS Package activity to assign their run-time values:

 

    -- ConnectionString: Use AAD authentication with ADF managed identity, e.g.

Data Source=YourAzureSQLDatabaseServerName.database.windows.net;User ID=;Initial Catalog=SSISDB;Provider=MSOLEDBSQL;Integrated Security=SSPI;Auto Translate=False;

    -- ConnectUsingManagedIdentity: True

Make sure that you configure your Azure SQL Database server to allow access using AAD authentication with ADF managed identity, see https://docs.microsoft.com/sql/integration-services/connection-manager/ole-db-connection-manager?view=sql-server-ver15#managed-identities-for-azure-resources-authentication.

    -- ExecuteOnProxy: False

    -- SQLStatementSource:

EXEC internal.cleanup_server_retention_window_exclusive

    -- SQLStatementSourceType: 1

 

 

If you have other scenarios that require you to assign run-time values to different properties that aren’t parameterized in our sample package, you can still do so on the Connection managers/Property overrides tabs of your Execute SSIS Package activity by referencing the component names (Execute SQL Task/OLEDB Connection Manager/File Connection Manager).

 

I hope you’ll find this article useful to run any SQL statements/script anywhere using SSIS in ADF.  Please don’t hesitate to contact us if you have any feedbacks, questions, or issues, and we’ll follow up ASAP.  Thank you as always for your support.