Pragmatic Works is excited to announce the release of the Azure Data Factory edition of Task Factory. This edition works with Azure Data Factory V2 and runs the same as the on-prem version. This edition is currently only available in beta but the full release is coming soon!
This detailed blog explains how to create an Azure SSIS Integration Runtime (IR) and install Task Factory during the startup process of the IR.
Currently, the only way to install SSIS third party components like Task Factory is by creating a main.cmd batch file that will run the installation and licensing commands during the startup process of the Azure SSIS IR.
You can read more about the general process of custom startup scripts for an Azure IR on the Microsoft website here: https://docs.microsoft.com/en-us/azure/data-factory/how-to-configure-azure-ssis-ir-custom-setup
There are a few restrictions with running Task Factory on ADF:
- USPS Address Verification will not work.
- Dimension Merge SCD is currently not available in beta.
- TF Excel Power Refresh Task – Unless you install Excel on the ADF IR, this will not work. You can install Excel via the installation script explained below and, in the link above.
- All components on ADF are considered BETA at this time. We will help you resolve any issue that arises, but you should be careful about using Task Factory on ADF for production level processes.
- There isn’t a trial version of TF to run on ADF SSIS IR at this time. This is due to the way installation and licensing is being handled in the IR for third party components.
Before you can activate your TF license, you’ll need to obtain an ADF ready license from us here [link to ADF License page]. The licenses come in 2, 4, 8, 16 and 32 node versions. You will need to invest in the appropriate license depending on how many nodes your SSIS IR will contain.
- An Azure Subscription
- An Azure SQL Database or Managed Instance Server
- Download and install Azure PowerShell (5.4 or above)
- Download and install Microsoft Azure Storage Explorer
Before we get into the details on how to install Task Factory, you need to go through a tutorial from Microsoft.
One thing not mentioned in the tutorial is that you will need an Azure SQL Server instance that does not have an SSISDB database. If you have an SSISDB database in the SQL Server you provide during the process of creating your Azure IR in the tutorial, the process of creating your IR will fail.
This means every time you want to create an SSIS IR, you need to either delete the current SSISDB (if it’s not being used) or create a new server without an SSISDB database.
The Microsoft tutorial assumes you will create a new SQL Server each time you create a new Azure SSIS IR.
Here is the tutorial - https://docs.microsoft.com/en-us/azure/data-factory/tutorial-deploy-ssis-packages-azure
Read through it and run through the process of setting up your Data Factory and Azure SSIS IR instance.
Because your Azure IR will start after you run through the tutorial, you will need to stop it before moving on to the next step.
To do this, click the Stop icon next to your Azure IR on the “Integration Runtimes” tab. This typically takes 20-30 minutes. After the IR has stopped, continue on.
Create Storage Container
Now that you’ve gone through the tutorial, it’s time to create a location for the SSIS IR to locate your main.cmd file. To do this, fire up Microsoft Azure Storage Explorer.
You’ll create a new Azure Blob Storage container.
Browse to your blob containers, right click on Blob Containers and select “Create Blob Container”
We named ours tf-install-files. You can name yours whatever you want.
Setup Your main.cmd File
Download the latest version of Task Factory from your Pragmatic Works account. Rename the installation file to “TaskFactory_Install.exe”.
Next, download our ADF Task Factory installation zip file from our public blob container. To do this, open Azure Storage Explorer and follow these steps:
- Under (Local and Attached), right-click Storage Accounts, select Connect to Azure storage, select Use a connection string or a shared access signature URI, and then select Next.
- Select Use a SAS URI and enter the following: SAS URI for the Public Preview container. Select Next, and then select Connect.
- Download the azure-configure-ssis-ir-taskfactory.zip file and extract the contents.
Open the main.cmd file and replace YOUR_KEY_HERE with your Task Factory Activation key located in your Pragmatic Works account.
call PragmaticWorks.CommandLineLicenseActivator.exe online YOUR_KEY_HERE >> %CUSTOM_SETUP_SCRIPT_LOG_DIR%\activation.log
After you’ve added your key, upload your edited main.cmd, TaskFactory_Install.exe, PragmaticWorks.CommandLineLicenseActivator.exe and PragmaticWorks.Common.dll to your blob storage container.
Your storage container should now look like this:
Create Your Shared Access Signature URL
For the Azure IR to be able to see those files, you’ll need to provide a Shared Access Signature to the script.
To create your SAS, simply right click on the blob container you just created and select “Get Shared Access Signature…”
You’ll be prompted with this dialog. Ensure you select the “Write” option in the Permissions section. Otherwise, the installation process cannot write a log file for the main.cmd, Task Factory Installation and activation processes.
Click “Create” and you’ll be provided a URL on the next screen. Copy and save that URL temporarily.
Now we’re going to set some properties on your SSIS Azure-IR that will enable the main.cmd file to execute when the IR is started.
To do this, open up the azure-configure-ssis-ir-taskfactory_rtm.ps1 in Azure Powershell that was extracted from the azure-configure-ssis-ir-taskfactory.zip you downloaded in a previous step.
You need to provide the parameter SetupScriptContainerSasUri which will contain the shared access signature URL created above.
Here’s what the full PowerShell method looks like with that parameter.
The other parameters are pretty self-explanatory, but here’s a brief description:
$SubscriptionName = The name of your Azure subscription
$ResourceGroupName = The name of the resource group the Data Factory belongs to
$DataFactoryName = The name of the Data Factory
$AzureSSISName = The name of the Azure SSIS
$SetupScriptContainerSasUri = The SAS URL created in the previous section of this post
Now that all your parameters are set up, it’s time to run the PowerShell script in the Azure PowerShell ISE.
Click the Run button in Azure Powershell ISE. You may be prompted to login to your Azure account. You may also get a prompt that asks you to replace your Azure IR. This is perfectly fine. Your IR will not be replaced. The IR will be updated with the SAS Container URL and started.
This process typically takes between 15 and 30 minutes.
To ensure everything was properly installed, open Microsoft Azure Storage Explorer and navigate to the storage blob container created in the “Create Storage Container” section of this post.
Any log files will be located under the main.cmd.log directory. If you had the Storage Explorer open already, you may need to refresh the window.
Review the log files for any errors. If you do not see any, you’re good to go. You shouldn’t have to review the Task Factory installation log unless you run a package with Task Factory and get a specific Task Factory failure message.
Deploying Packages to Azure SSIS IR
Now that your Azure IR has been created and Task Factory is installed, you should be able to take any package created locally in SSDT, deploy it to Azure IR and execute it successfully.
To deploy your packages to your Azure IR, follow this tutorial. https://docs.microsoft.com/en-us/sql/integration-services/lift-shift/ssis-azure-deploy-run-monitor-tutorial
Keep in mind that you MUST have the latest version 17.2 of SQL Server Management Studio for this tutorial above to work. You will not be able to see Integration Service Catalog without the latest version.
If you run into any issues with trying to install or activate Task Factory in your Azure SSIS IR, please do not hesitate to contact our support team at email@example.com