Free Webinar – Designing a Custom ADF SSIS Execution Framework

Folks, I’m not going to even pretend to be cool and collected about this webinar about designing an ADF SSIS Execution Framework. I’ve been waiting for this one for a loooooooong time! The next (free!) webinar in the series I’m calling the Summer-O’-ADF is Designing a Custom ADF SSIS Execution Framework.

It’s at noon EDT Thursday 12 Jul 2018.

Why Am I So Excited?

I’ve been designing data engineering frameworks since the days of DTS (Data Transformation Services). Enterprise Data & Analytics (EDNA) implements SSIS Frameworks for enterprises. We even give away a free version of a framework at DILM Suite (DILM == Data Integration Lifecycle Management). It’s called SSIS Framework Community Edition and it’s not only free, it’s open source.

I wrote about SSIS Framework Community Edition and Data Integration Lifecycle Management in my latest book: Data Integration Lifecycle Management with SSIS.

I’m excited about this webinar because it combines a long-held passion – DevOps for SSIS – with a new passion – Azure Data Factory (ADF)! I cannot wait to show you how!

If you haven’t seen my webinars titled The Azure Data Factory Controller Design Pattern and ADF Controller Design Pattern with the SSIS Integration Runtime (also part of the Summer-O’-ADF webinar series), you may want to check them out first (registration required).

Register today!


Free Webinar – ADF Controller Design Pattern with the SSIS Integration Runtime

The next free webinar in my Summer-O’-ADF series of free webinars is titled ADF Controller Design Pattern with the SSIS Integration Runtime. It’s scheduled for Thursday 5 Jul 2018 at noon EDT. It will be recorded in case you miss it.

“Previously on the Summer-O’-ADF Series…”


In my previous webinar, The Azure Data Factory Controller Design Pattern (recording available, registration required), I demonstrated using the Controller Design Pattern is “native Azure Data Factory.” In native ADF, I used the Execute Pipeline Activity to start a different ADF pipeline.

I walked through several use cases, demonstrating the behavior of the Execute Pipeline and the implications of important property configurations. Check it out!

“In This Episode of the Summer-O’-ADF Series…”

ADF Controller Design Pattern

In this webinar, I apply similar logic to the ADF Execute SSIS Package Activity! Join me as we walk through configurations and use cases for the Execute SSIS Package activity in Azure Data Factory.

Register today!


It’s Biml 2018 Release Day!

Scott Currie and the team at Varigence announce the release of BimlFlex 2018, BimlStudio 2018, and BimlExpress 2018!

This much-anticipated release includes awesome features for experienced Biml professionals as well as for data engineers new to automating processes!

BimlFlex 2018

BimlFlex is a mature and complete data engineering automation framework. Out of the box, BimlFlex automates several data models, including:

  • Staging Database
  • Persisted Staging Area (ODS)
  • Raw Data Valut
  • Business Data Vault
  • Dimensional Data Warehouse
  • Data Marts
  • Cubes
  • Tabular

The benefits of BimlFlex:

  1. Flexible Data Framework
  2. Robust Metadata Mapping Tool
  3. Avoid Data Debt
  4. Shorter Delivery Times
  5. Simplified Maintenance
  6. Upfront Pricing


I absolutely love the BimlFlex Savings Calculator near the bottom of the page (click to enlarge)!

BimlStudio 2018

BimlStudio 2018 includes support for all Azure Data Factory (ADF) version 2 items (as of 06/20/2018). Which ADFv2 items does BimlStudio 2018 support?

  • Linked Services
  • DataSets, Sources, and Sinks
  • Pipelines and Activities (including control flow)
  • Triggers

Also included is support for BimlScript PreCompiled Assembly Package (BSPCAP) files, which include the preprocessed binary assets for all of the BimlScript files in your project.

BSPCAP promises faster processing – especially with large codebases – for builds in the interactive designer and for command line builds. I’m looking forward to learning more about this feature!

There are too many new features to list here – go to the BimlStudio 2018 page and check them all out!

BimlExpress 2018

Varigence keeps giving away cool stuff! Nowhere is Varigence’s commitment to community more evident than in the feature list for BimlExpress 2018. The previous version – BimlExpress 2017 – included the Preview Pane. BimlExpress 2018 includes the ability to Convert SSIS Packages to Biml:

How cool is that? And it’s in the free (FREE!) version!

As with BimlFlex and BimlStudio, there are too many cool features to list here. Head over to the BimlExpress 2018 feature page to learn more.

And Happy Biml’ing!


Free Webinar – The Azure Data Factory Controller Design Pattern – 28 Jun

Andy’s Summer-o’-ADF continues with another free (yes, FREE) webinar!



Join me as I begin to explore a brand new topic: Azure Data Factory Design Patterns. This first webinar in the series is titled The Azure Data Factory Controller Design Pattern. In this demo-tastic session I show how to apply the pattern described in this post – titled SSIS Design Pattern: Controller Pattern – to ADF.

Registration is required to attend The Azure Data Factory Controller Design Pattern.

Sign up today!

In conclusion, I hope to see you there.


The Recording for Introduction to Lifting and Shifting SSIS to the Cloud is Now Available


The recording of my free webinar – Introduction to Lifting and Shifting SSIS to the Cloud – is now available (registration required).


  • How does an enterprise lift and shift SSIS projects from SSIS Catalogs on-premises to the Azure Data Factory Integration Runtime?
  • What are the best practices?
  • Do SSIS lifecycle management tools exist to support SSIS lift and shift?

Join Andy Leonard – SSIS author, trainer, and consultant – to learn the answers to these questions. In this webinar, Andy discusses and demonstrates migrating SSIS to the Azure Data Factory Integration Runtime.


You may want to hire Enterprise Data & Analytics to help your enterprise SSIS to Azure Data Factory because we grok ADF Integration Runtime.

Contact us today.


ADF Execute SSIS Package Activity

The good people who work on Azure Data Factory recently added an Execute SSIS Package activity. It’s pretty cool. Let’s tinker with it some, shall we?

First, you will need to create an Azure Data Factory SSIS Integration Runtime. If you don’t know how, that’s ok – I’ve written a post titled Lift and Shift SSIS Part 0: Creating the ADF Integration Runtime that describes one way to set up ADFIR.

0. Connect to Azure Data Factory

You can connect to the Azure Data Factory (ADF) dashboard here. After you connect…

1. Navigate to the Author Page

The “author” is indicated by the pencil icon on the left side of the ADF dashboard:

On the Author page you may add ADF resources by clicking the “+” symbol beside the search textbox. The dropdown menu contains three items at the time of this writing: Pipeline, Dataset, and Copy Data. Select Pipeline:

2. Drag an Execute SSIS Package Activity Onto the Surface

Click  the new Execute SSIS Package activity to edit it, clicking the tabs shown below the Azure Data Factory Integration Runtime (ADFIR) surface. Configure the Execute SSIS Package activity name on the General tab:

3. Configure the SSIS Package to Execute

Configure the Execute SSIS Package activity’s SSIS Package on the General tab. Set the Catalog Package Path property to <folder>/<project>/<package> as shown below (click to enlarge):

I can hear you thinking, “But Andy, what if I don’t know the Package Path?”

Fear not. The next release of Catalog Browser – a free utility from the DILM Suite (Data Integration Lifecycle Management Suite) – displays the Catalog Package Path when you click on an SSIS Package:

Please note: Catalog Browser displays backslashes in the Catalog Package Path and ADFIR’s Execute SSIS Package activity expects forward slashes.

The next version of Catalog Browser is scheduled for release on or before 1 Jul 2018.

That’s it. A quick Debug execution will verify the ADF pipeline executes:

Use SQL Server Management Studio (SSMS) to connect to the server hosting your Azure Data Factory Integration Runtime. View the All Executions report and confirm the execution happened and succeeded:

For more information about using SSMS to connect to ADFIR, please see the post titled Lift and Shift SSIS Part 1: Deploy Integration Runtime SSIS.

Learn More:
Free webinar: Introduction to Lifting and Shifting SSIS to the Cloud
Andy Leonard
21 Jun 2018 at noon EDT
Register today!


Lift and Shift SSIS Part 1: Deploy Integration Runtime SSIS

What if I told you, “You can quickly and easily deploy Integration Runtime SSIS projects to Azure Data Factory?” You can! As shown in the image, you can simply right-click the SSIS project name in SQL Server Data Tools (SSDT) Solution Explorer and click Deploy to deploy the project to the Azure Data Factory (ADF) Integration Runtime. You can also start the Integration Services Deployment Wizard by navigating to an ISPAC file using Windows Explorer and double-clicking it.

In Part 0 we learned how to create and configure an Azure Data Factory Integration Runtime. In this post we connect to ADFIR, deploy an SSIS project, and examine options for viewing the contents of the ADFIR SSIS Catalog.

First, Deploying SSIS

Follow the Wizard… the Integration Services Deployment Wizard, that is. Connect to the Destination and create a Catalog folder if needed:

Next comes Validation. Be sure to address any errors that appear here:

Review is next:

And then, finally, Deploying Integration Runtime SSIS projects is as easy as deploying SSIS projects to an SSIS Catalog on-premises:

There’s really very little difference between deploying an SSIS project to an on-premises instance of an SSIS Catalog and deploying an SSIS project to an instance of the ADF Integration Runtime.

That. Is. Awesome!

What Happens When You Deploy Integration Runtime SSIS Projects?

Remember all the waiting back in Part 0? ADF was creating a copy of the SSISDB database – which contains all the goodies for the SSIS Catalog. that’s right – there’s a real, live copy of an SSIS Catalog stored in the database on the server you created in Part 0. You can view it in the very same way you would view an SSIS Catalog that resides on-premises: using the Integration Services Catalog node in Object Explorer in SQL Server Management Studio (SSMS).

Viewing the Catalog in SQL Server Management Studio (SSMS)

Open SSMS and begin connecting to a database:

Before you click Connect, click the Connection Properties tab and change the “Connect to Database” property to SSISDB:

You should now be able to view the ADF SSIS Integration Runtime using the SSMS Object Explorer’s Integration Services Catalogs node:

If you do not change the “Connect to Database” property to SSISDB, you can still connect to the Azure SQL DB instance, but you will not see the Integration Services Catalogs node. Instead, you will see nodes for Databases and Security:

All is not lost. Simply reconnect – this time remembering to change the “Connect to Database” property to SSISDB on the Connection Properties tab.

Viewing the Catalog with SSIS Catalog Browser

You can also use SSIS Catalog Browser (it’s free!) from DILM Suite to surface contents of the SSIS Integration Runtime. Simply connect to the ADFIR server:

Catalog Browser surfaces rich SSIS Catalog in a unified view:

Cool, huh?

Viewing the Catalog with SSIS Catalog Compare (v3 Preview)

One last thing…

SSIS Catalog Compare version 3 is in Preview (want a copy? Contact me!) and facilitates direct lift and shift from on-premises SSIS Catalogs to ADF Integration Runtime SSIS Catalogs:

Data engineers can use SSIS Catalog Compare to deploy directly to ADFIR or to generate scripts for:

  • Entire Catalogs
  • Catalog Folders
  • SSIS Projects
  • Literal Overrides
  • Catalog Environments
    • Catalog Environment Variables
  • References
    • Reference Mappings

For more information, visit DILM Suite SSIS Catalog Compare.


Don’t forget to tear it down when you are done!

Lastly, some Azure resources are expensive. You may not desire to leave ADFIR running, especially if you’re done tinkering and learning.

Learn More:
Free webinar: Introduction to Lifting and Shifting SSIS to the Cloud
Andy Leonard
21 Jun 2018 at noon EDT
Register today!


Lift and Shift SSIS Part 0: Creating the ADF Integration Runtime

Let’s face it: All the cool kids are playing in the cloud. And why wouldn’t they be? Microsoft Azure gets new features every week… sometimes every day! One shiny feature is Azure Data Factory Integration Runtime (ADFIR) which is new in Azure Data Factory version 2. In this post I will show you one way to create the Integration Runtime.

Connect to Azure and Create a SQL DB

First, connect to the Azure Portal. Click the “Create a resource” link and add a new SQL DB and server. I like to create a new resource group as well because a resource group allows me to group together a bunch of related resources. It’s also easier to tear down once I’m done. (Who wants to pay extra for leaving things running after you’re done tinkering? Anyone? Bueller? Bueller?)

Create an Azure Data Factory

Next, click the “Add a resource” link, select Integration from the topics on the left, and then click Data Factory to create an Azure Data Factory:

Walk through the configuration steps, selecting unique names where required. I tend to choose “lesser” options when they are presented. Again, I do not wish to pay for resources unless and until I need them.

Once your Azure Data Factory is configured you can browse to the Author & Monitor site:

When you reach the ADF Author & Monitor site, click the image and link to “Configure SSIS Integration Runtime”:

Configuring the SSIS Integration Runtime

There are three steps to configure the SSIS Integration Runtime:

1) Set the Name, Location, Node size and number (click to enlarge):

2) Configure SQL Server settings (click to enlarge):

3) Configure Maximum Executions per Node and Advanced Connections settings (click to enlarge):


At the time of this writing it takes 20-30 minutes for the Azure Data Factory Runtime to be created:

It will finally start. Promise:


That’s it! You’ve configured Azure Data Factory’s Integration Runtime.

See Part 1 to learn more about deploying SSIS projects to your shiny new ADFIR!

Learn More:
Free webinar: Introduction to Lifting and Shifting SSIS to the Cloud
Andy Leonard
21 Jun 2018 at noon EDT
Register today!