Following our first blog in the “3 Easy Steps” series, see https://techcommunity.microsoft.com/t5/sql-server-integration-services/run-any-sql-anywhere-in-3-easy-steps-with-ssis-in-azure-data/ba-p/2457244, we’d like to show you more ways to quickly extend ADF capabilities by leveraging 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 process, which can be any executable, such as application/program/utility/batch file, 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 executable on premises and in the cloud. To run it 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 checkboxes to install Azure PowerShell as an express custom setup and configure SHIR as a proxy on the Advanced settings page, see https://docs.microsoft.com/azure/data-factory/tutorial-deploy-ssis-packages-azure.
Your SSIS IR 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 (ExecuteProcess.dtsx) on SQL Server Data Tools (SSDT) containing two components named after their actual type/function: Flexible File Task to optionally download an input file for your executable onto the working directory of your SSIS IR and Execute Process Task to run your executable on SHIR/SSIS IR. We’ve also parameterized the following package properties for you to assign your run-time values:
-- Arguments: Enter the command-line arguments for your executable.
-- Executable: Enter the name of your executable to run.
-- ExecuteOnProxy: Enter True or False to run Execute Process Task on your SHIR or SSIS IR, respectively.
-- SourceFileName: (OPTIONAL) Enter the name of input file for your executable, so Flexible File Task can download it onto the working directory of your SSIS IR – Use only when you want to run your executable on SSIS IR and it can’t accept UNC path as an argument, otherwise omit it.
-- SourceFolderPath: (OPTIONAL) Enter the path of folder containing an input file for your executable, so Flexible File Task can download it onto the working directory of your SSIS IR – Use only when you want to run your executable on SSIS IR and it can’t accept UNC path as an argument, otherwise omit it.
-- WorkingDirectory: Enter the path of folder containing your executable – If omitted, your executable must be located in one of the directories included in Windows %PATH% environment variable.
You can download our sample package from https://ssisazurefileshare.blob.core.windows.net/blogs/ExecuteProcess.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 command-line utility on your machine on premises
In this example, we’ll run the bulk copy program (bcp) utility to export data from your SQL Server on premises into a CSV file. To do so, 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:
-- Arguments: YourDBName.dbo.YourTableName out C:\YourFolderName\YourFileName.csv -c -t, -T
-- Executable: bcp
-- ExecuteOnProxy: True
Example 2) Run any batch file on your machine on premises
In this example, we’ll run a main.cmd batch file that will in turn run the bcp utility to export data from your SQL Server on premises into a CSV file and a PowerShell script to upload the CSV file into a new blob container in your Azure Storage. To do so, 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:
-- Executable: main.cmd
The batch file contains the following commands:
bcp YourDBName.dbo.YourTableName out C:\YourFolderName\YourFileName.csv -c -t, -T
powershell -File C:\YourFolderName\YourScriptName.ps1
The PowerShell script contains the following cmdlets:
$ctx = New-AzStorageContext YourAzureStorageAccountName YourAzureStorageAccountKey
New-AzStorageContainer YourContainerName -Context $ctx -Permission blob
Set-AzStorageBlobContent -File C:\YourFolderName\YourFileName.csv -Container YourContainerName -Blob YourFileName.csv -Context $ctx
-- ExecuteOnProxy: True
-- WorkingDirectory: C:\YourFolderName\
Example 3) Run any package stored in file system on your machine on premises
In this example, we’ll run a package stored in file system that connects to your SQL Server on premises. To do so, 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:
-- Arguments: /File C:\YourFolderName\YourPackageName.dtsx
-- Executable: dtexec
-- ExecuteOnProxy: True
Example 4) Run any Azure PowerShell cmdlets on your SSIS IR
In this example, we’ll run Azure PowerShell cmdlets to create a new blob container in your Azure Storage. To do so, 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:
-- Arguments: $ctx = New-AzStorageContext YourAzureStorageAccountName YourAzureStorageAccountKey; New-AzStorageContainer YourContainerName -Context $ctx -Permission blob
-- Executable: powershell
-- ExecuteOnProxy: False
Example 5) Run any Azure PowerShell script file on your SSIS IR
In this example, we’ll run an Azure PowerShell script file stored in Azure Files. To do so, complete the following steps:
a) 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.
b) Add the following parameters on the SSIS parameters tab of your Execute SSIS Package activity to assign their run-time values:
-- Arguments: -F YourScriptName.ps1
-- Executable: powershell
-- ExecuteOnProxy: False
-- SourceFileName: YourScriptName.ps1
-- SourceFolderPath: //YourAzureStorageAccountName.file.core.windows.net/YourFileshareName/
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 (Flexible File Task/Execute SQL Task).
I hope you’ll find this article useful to run any process/executable 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.
Microsoft
