UPDATE: View the recording of the free webinar titled Execute Azure-SSIS from Azure Files. Enjoy! :{>
Join me Tuesday, 2 Jul 2019 at 12:00 PM EDT for a free webinar titled Execute Azure-SSIS Packages from Azure Files!
An Updated Azure-SSIS Integration Runtime
I confess: I have been waiting for this feature since I first learned of Azure-SSIS.
When I first saw Azure-SSIS – which creates an Azure Data Factory Integration Runtime and SSIS Catalog in the cloud, my first thought was a paraphrase Ferris Bueller’s question about dying the river green: “If we can execute SSIS packages from the SSIS Catalog in Azure Data Factory, why can’t we execute SSIS packages from Azure Blob Storage?” Today, we can.
New, Shiny, and Different
Azure-SSIS has been updated to work with or without an SSIS Catalog:
During Integration Runtime setup, SSIS developers now have an option to provision an SSIS Catalog or not. Uncheck the “Create SSIS catalog (SSISDB)…” checkbox to create an Azure-SSIS instance for executing SSIS packages from Azure Files.
SSIS Developers have the option to create more than one instance of Azure-SSIS file execution instances:
Setup an Azure-SSIS IR to Execute From Azure Files
You may provision an Azure-SSIS file execution instance from the ADF Overview page:
Provide a name and optional description for the Azure-SSIS instance. Configure the Location, Node Size, Node Number, and Edition properties. Note you can save money if you already own a license for SQL Server. Click the Next button to proceed:
The next screen initially appears as shown:
Uncheck the “Create SSIS catalog (SSISDB)…” checkbox to create an Azure-SSIS instance for executing SSIS packages from Azure Files:
No other configuration is required, click the Next button to proceed.
If you use third-party or custom SSIS tasks and components, provide the SAS URI for the Custom Setup Container:
If you desire to access the Azure-SSIS instance via VNet, check the “Select a VNet for your Azure-SSIS Integration Runtime…” checkbox and configure VNet access settings:
Review settings on the Summary page of the Azure-SSIS Integration Runtime Setup blade. Click the Finish button to complete the setup process:
In just a few minutes, the new Azure-SSIS file execution IR is up and running:
One Way to Use Azure-SSIS File Execution to Execute SSIS Packages
This is but one way to configure an SSIS package to execute from Azure Files. You will find more information in the article titled Run an SSIS package with the Execute SSIS Package activity in Azure Data Factory.
Once the new and shiny Azure-SSIS file execution IR is up and running, you may use it to execute SSIS packages stored in Azure Files.
First, store an SSIS package in Azure Files. Open Microsoft Azure Storage Explorer (it’s free here) and connect to an Azure Files repository. Upload SSIS package files if necessary (I am using my public SSIS Samples / Test SSIS Solution project, uploaded to a folder named test1):
Create a new ADF pipeline named “TestSSISFileExecution” and add an Execute SSIS Package Activity named “Execute SSIS File”:
On the Settings tab, select the name of your Azure-SSIS File Execution Integration Runtime. Select File System (Package) to execute a single SSIS package or File System (project) to interrogate an ISPAC file for the SSIS package to execute. Please note: The Execute SSIS Package activity will execute a single SSIS package:
Executing an SSIS Package stored in Azure Files and Not Using Azure Key Vault
I choose to not use Windows Authentication.
I choose to use the 64-bit runtime.
I choose to execute an SSIS Package stored in Azure Files.
I choose to not use Azure Key Vault to store credentials.
I choose to not configure logging.
Why? Because I think this is the most straightforward example, an example that has the fewest moving parts. So, onward…
SSIS Package and DTSConfig Files in the Blue Box
In the image below I’ve added a blue box and a red box. The blue box contains the Azure Files storage account path to the SSIS package I wish to execute and the path to a configuration file (dtsConfig). The Azure-SSIS team is leveraging the fact that SSIS packages may still be developed using Package Deployment Model for backwards-compatibility. Package Deployment Model supports SSIS Package Configurations, such as DTSConfig files. (Note: I do not know if other SSIS Package Configuration options are available – but I seriously doubt it.)
In my example, I do not include a DTSConfig file (again: most straightforward, fewest moving parts).
The package path is composed: \\yourstorageaccount.file.core.windows.net\yourfileshare\yourpackage.dtsx. The backslashes are important. This is not the same as the URL to the SSIS Package stored in Azure Files. The URL includes “https://” and forward slashes all around.
In the blue box below, please find a Package Path configured as “\\<mystorageaccount>.file.core.windows.net\fs-ssis\ReportAndSucceed.dtsx”. Please note <mystorageaccount> is a placeholder for the name of, well, my storage account.
The name of my File Share is “fs-ssis”.
The name of my SSIS Package is “ReportAndSucceed.dtsx”.
Credentials in the Red Box
If you are executing SSIS from an Azure File Share and you are not using Windows Authentication, enter “AZURE” into the Domain textbox.
This is tricksy…
In the Username textbox, enter the name of the Azure Files account.
This is the same name identified as <mystorageaccount> above.
This is tricksier…
In the Password textbox, enter an Azure Storage Account Access Key.
You can obtain the storage account key using Microsoft Azure Storage Explorer. Connect to your account, navigate to the Storage Accounts folder, right-click the Storage Accounts folder, and then click either “Copy Primary Key” or “Copy Secondary Key” as shown to the left. You are copying an Azure Storage Account Access Key to the clipboard.
Logging in the Green Box
I’ve identified logging in the green box, but I have not shown how to configure it. Usually, I enter the same Azure Files folder used to store the SSIS Package – “\\<mystorageaccount>.file.core.windows.net\fs-ssis” – and check the “Same as package access credentials” checkbox for the “Logging access credentials” property.
When completed, the Execute SSIS Package activity should appear similar to that shown below:
Test It!
Test the pipeline by clicking the Debug button:
The ReportAndSucceed.dtsx SSIS package – available here – executes in about 30 seconds:
Conclusion
Executing SSIS from a cloud-based file store is a welcome extension to Azure-SSIS.
Kudos to the Microsoft Azure-SSIS Team for facilitating more cool Azure-SSIS functionality!
Just to be clear, this is used to execute SSIS package in the package deployment model, right?
Hi Koen,
I am not certain.
Although I can find nothing mentioned in the documentation, I assume Package Deployment Model (or Package Deployment Model-like – meaning, no Project Deployment Model features) packages are executed. That said, it bears testing.
Good catch.
:{>
Hi Andy,
I followed your example because one of my clients is blocking the deployment of SSIS packages to the SSIS catalogue in Azure. By using the blob storage I hope to have an alternative. But… my package is failing as it indicates that there is a USER ERROR (“errorCode”: “2906”)? Any suggestion ( I follow exactly your way of doing) ?
Hi Tim,
Sandy Winarko answered a question about the 2906 error here (https://social.msdn.microsoft.com/Forums/azure/en-US/e12acfa1-a3d2-4130-be36-67cefd2a51bc/execution-ssis-package-failed-within-adfv2-pipeline-but-works-manually-pls-help-me?forum=AzureDataFactory), indicating a permissions / connection issue with the Linked Service. Executing from Azure File Share requires permissions for connections as well. My advice is to write very small – one Execute SQL Task or Data Flow for each connection – each connecting ot a single source or destination, deploy them to the File Share and see if you can get each to execute.
Hope this helps,
Andy