Last night I presented Moving Data with Azure Data Factory to a packed house at the Richmond Azure User Group. The crowd was engaged and asked awesome questions. I had a blast!
Surprise!
I rehearsed many of my demos before the presentation and even updated my slides. One thing I did not do was rehearse configuring an Execute SSIS Package activity. Why? I’ve built these demos live a bajillion times. I know what I’m doing…
When I clicked on the Settings tab I found new stuff – options I’d not previously encountered. Thankfully, I immediately recognized the purpose of these new configuration options – and I also immediately liked them. The demo worked and no one was the wiser (except the handful of folks with whom I shared this story after the meeting).
New Dropdowns for Folder, Project and Package
The new interface sports new dropdowns for selecting the Catalog Folder, Project, and Package to executed. I like this – it’s slick. I had deployed a project to my Azure-SSIS instance between the time I started this part of the demo and the time I wanted to configure the Execute SSIS Package activity. During deployment I created a new Catalog Folder which was not initially listed in the Folder dropdown. Clicking the Refresh button remedied this, though, and I was able to complete configuration rapidly.
Configuration Tabs
I cannot recall if the Connection Managers and Property Overrides tabs were part of the previous Execute SSIS Package activity interface. I don’t think so, but I could be wrong about that. Update: I verified these are new tabs by looking at screenshots from my June 2018 post titled ADF Execute SSIS Package Activity. The previous version had an Advanced tab.
The SSIS package configuration tabs are SSIS Parameters, Connection Managers, and Property Overrides.
When your Azure-SSIS instance is running, you may use these tabs to update Parameter, Connection Manager Property, and SSIS Package Property values:
Warnings
It’s possible to configure the Execute SSIS Package activity when your Azure-SSIS Integration Runtime is not running, but you don’t get the nice dropdown pre-population and have to revert to the previous method of configuring the full SSIS Catalog path to the package you desire to execute.
SSIS Catalog Browser To The Rescue!
If you find yourself in this predicament and would rather configure the Execute SSIS Package activity without waiting 20-30 minutes for the Azure-SSIS instance to spin up, you can use SSIS Catalog Browser – a free utility from DILM Suite – to connect to your Azure-SSIS instance:
SSIS Catalog Browser displays the Catalog path for an SSIS package (or Catalog Environment) when you select the artifact in the unified Catalog surface. Copy the package’s Catalog path displayed in the Status area and and paste the value into the Package Path textbox in ADF:
Make sure the Manual Entries checkbox is checked.
I like the warnings. Feedback is a good thing.
Once Azure-SSIS Is Running
When your Azure-SSIS instance is up and running, you may configure the path to your SSIS package using the dropdowns:
You can even configure the path to a Catalog Environment:
Conclusion
I believe there are at least two lessons to take away from my experience:
- When presenting on Microsoft Azure topics, always check your demos to make certain nothing has changed; and
- Microsoft Azure is evolving at a rapid rate – especially Azure Data Factory!
Hi Andy, I’ve encountered an issue with this system when deploying a data factory through multiple environments. I want the SSIS Activity to pick up the parameters from the Catalogue of the environment the data factory is deployed to. However the parameters tab does not change unless you re-enter the catalogue information into the Setting tab on the SSIS activity. Having to do this makes deployments hard to automate. I’m using the environmental variables rather than overwriting in the values in the package as the team I work with are more comfortable working with SSIS. Have you seen this issue before as any advice would be gratefully received?
Hi Phil,
I’ve not encountered this issue, but I will forward it to my contacts on the SSIS team.
Hope this helps,
Andy
Hi Andy,
Really appreciate you raising this issue for me and for coming back so quickly. I was just about to post a “just realised..” follow up. In my deployment process I was deploying the Data Factory first and then the SSIS packages last. I think it may have been because the integration runtime was still running following the SSIS deployment during the deployment of the data factory and the adf deployment could therefore refresh this information during this process whereas normally this is onlty running during ETL . Thanks again Phil