Our state-of-the-art DILM Suite tools in the capable hands of our experienced data engineers combine to drastically reduce the amount of time to manually migrate and apply SSIS Catalog configuration artifacts – Literals, Catalog Environments and Catalog Environment Variables, References, and Reference Mappings – while simultaneously improving the quality of the migration effort.
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!
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.
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:
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:
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!
I’m honored to deliver Moving Data with Azure Data Factory to Azure Richmond Virginia Thursday, 7 Feb 2019 starting at 6:30 PM! The meeting will be held at Markel Plaza – 4600 Cox Road · Glen Allen, VA.
The next delivery is 01-02 Apr 2019, 9:00 AM – 4:30 PM ET.
Data integration is the foundation of data science, business intelligence, and enterprise data warehousing. This instructor-led training class is specifically designed for SQL Server Integration Services (SSIS) professionals responsible for developing, deploying, and managing data integration at enterprise-scale.
You will learn to improve data integration with SSIS by:
Building faster data integration.
Making data integration execution more manageable.
Building data integration faster.
SSIS Design Patterns for Performance – how to build SSIS packages that execute and load data faster by tuning SSIS data flows and implementing performance patterns.
SSIS Deployment, Configuration, Execution, and Monitoring – the “Ops” part of DevOps with SSIS using out-of-the-box tools and the latest utilities.
Automation – how to use Business Intelligence Markup Language (Biml) to improve SSIS quality and reduce development time.
The next delivery is 04 Mar 2019, 9:00 AM – 4:30 PM ET.
Azure Data Factory, or ADF, is an Azure PaaS (Platform-as-a-Service) that provides hybrid data integration at global scale. Use ADF to build fully managed ETL in the cloud – including SSIS. Join Andy Leonard – author, blogger, and Chief Data Engineer at Enterprise Data & Analytics – as he demonstrates practical Azure Data Factory use cases.
In this course, you’ll learn:
The essentials of ADF
Developing, testing, scheduling, monitoring, and managing ADF pipelines
Lifting and shifting SSIS to ADF SSIS Integration Runtime (Azure-SSIS)
ADF design patterns
Data Integration Lifecycle Management (DILM) for the cloud and hybrid data integration scenarios
I’ve been making smaller, more incremental changes to SSIS Catalog Browser – a free utility from the Data Integration Lifecycle Management suite (DILM Suite).
You can use SSIS Catalog Browser to view SSIS Catalog contents on a unified surface. Catalog Browser works with SSIS Catalogs on-premises and Azure Data Factory SSIS Integration Runtime, or Azure SSIS. It’s pretty cool and the price ($0 USD) is right!
The latest change is a version check that offers to send you to the page to download an update. You will find this change starting with version 0.7.7.0. Version 0.7.8.0 includes a slightly better-formatted version-check message. As I said, smaller, more incremental changes.
It’s free. That makes it difficult to beat the cost.
SSIS Catalog Browser is designed to surface all SSIS Catalog artifacts and properties in a single view. “What exactly does that mean, Andy?” You’re sharp. Let’s talk about why the surface-single-view is important.
Before I go on, you may read what I’m about to write here and in the companion post and think, “Andy doesn’t like the Integration Services Catalogs node in SSMS.” That is not accurate. I do like the Integration Services Catalogs node in SSMS. It surfaces enough information for the primary target user of SSMS – the Database Administrator – to see what they need to see to do their job, without “cluttering up” their interface with stuff that they rarely need to see and even more-rarely change.
In the companion post I shared this image of the windows (and pages) you need to open in SSMS to view the configured execution-time value of a parameter that is mapped via reference:
That’s a lot of open windows.
So how does one view the same configuration metadata in SSIS Catalog Browser?
Under the Project node (LiftAndShift), we find a virtual folder that holds Project Parameters.
In Project parameters, we find a reference mapping – indicated by underlined text decoration and describing the reference mapping as between the parameter (ProjectParameter) and the SSIS Catalog Environment Variable (StringParameter).
Expanding the reference mapping node surfaces References. There are two references named env1 and env2. Since references can reference SSIS Catalog Environments in other Catalog folders, the fully-qualified path to each SSIS Catalog environment is shown in folder/environment format.
Expanding each reference node surfaces the value of the SSIS Catalog Environment Variable in each SSIS Catalog Environment.
I call this feature Values Everywhere, and I like it. A lot.
Values Everywhere From the Project Reference Perspective
Values Everywhere is perspective-aware. Whereas from the perspective of an SSIS Project Parameter, Values Everywhere surfaces the reference mapping in the format parameter–>environment variable, in the Project References virtual folder, Values Everywhere surfaces the same relationship as environment variable–>parameter:
Values Everywhere perspectives follow intuition when surfacing reference mapping relationships. (Did I mention I like this feature? A lot?)
SSIS Catalog Browser provides a clean interface for enterprise Release Management and Configuration teams. And it’s free.
I can hear you thinking, “Why is Catalog Browser free, Andy?” I give away Catalog Browser to demonstrate the surfacing capabilities of SSIS Catalog Compare.
SSIS Catalog Compare
SSIS Catalog Compare not only surfaces two SSIS Catalogs side by side, you can compare the contents of the Catalogs:
You can also script an entire SSIS Catalog which produces T-SQL script and ISPAC files for every artifact in the SSIS Catalog (organized by instance and folder):
You can also deploy all artifacts contained in an SSIS Catalog Folder from one SSIS Catalog to another:
This functionality is an efficient method for Data Integration Lifecycle Management – or DevOps – with SSIS.
SSIS Catalog Compare even works with Azure Data Factory SSIS Integration Runtime, so you can use SSIS Catalog Compare to lift and shift SSIS from on-premises Catalogs to the cloud.
This is the first time I am delivering this session. It still has that new presentation smell!
Azure Data Factory – ADF – is a cloud data engineering solution. ADF version 2 sports a snappy web GUI (graphical user interface) and supports the SSIS Integration Runtime (IR) – or “SSIS in the Cloud.”
Attend this session to learn: – How to build a “native ADF” pipeline; – How to lift and shift SSIS to the Azure Data Factory integration Runtime; and – ADF Design Patterns to execute and monitor pipelines and packages.
SSIS Framework Community Edition is free and open source. You may know can use SSIS Framework Community Edition to execute a collection of SSIS packages using a call to a single stored procedure passing a single parameter. But did you know you can also use it to execute a collection of SSIS packages in Azure Data Factory SSIS Integration Runtime? You can!
In this free webinar, Andy discusses and demonstrates SSIS Framework Community Edition – on-premises and in the cloud.
Join SSIS author, BimlHero, consultant, trainer, and blogger Andy Leonard at noon EDT Thursday 20 Sep 2018 as he demonstrates using Biml to make an on-premises copy of an Azure SQL DB.
The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.