DILM Suite was designed to support enterprise data engineering / data integration with SSIS. But the solutions, utilities, and tools work well with Azure Data Factory Integration Runtime (ADF IR, or “SSIS in the cloud”).
As I mentioned in my post titled The Cloud Costs Money, leaving ADF Integration Runtime running can get pricey – and fast.
Most DILM Suite Functions Do Not Require ADF Integration Runtime to be Running
That’s right. Since DILM Suite tools and utilities connect directly to the database, they do not require Azure Data Factory Integration Runtime to be in a running state for most operations.
It’s been a busy couple weeks here at Enterprise Data & Analytics in Farmville. I just published updates to not one, not two, but three – yes, three! – applications that are part of the Data Integration Lifecycle Management Suite, or DILM Suite.
SSIS Framework Community Edition
To prepare for Thursday’s (12 Jul 2018) webinar titled Designing a Custom ADF SSIS Execution Framework, I updated the documentation for SSIS Framework Community Edition. Why did I update the documentation? Because I want folks to know they can use SSIS Framework Community Edition in the cloud! That’s right, SSIS Framework Community Edition – still free, still open source – works with the SSIS Catalog behind Azure Data Factory Integration Runtime.
SSIS Framework Browser
Also in preparation for Thursday’s webinar, I updated SSIS Framework Browser! Version 0.4.2.0 (beta) remains free and now also works with SSIS Framework Community Edition implemented in Azure Data Factory Integration Runtime. Use Framework Browser to view metadata for SSIS Applications and Application Packages stored in the Framework.
SSIS Catalog Browser
Finally, I updated the login experience for those using SSIS Catalog Browser to connect to Azure Data Factory Integration Runtime. As with all Catalog Browser updates, I’ve applied the same updates to the user experience in SSIS Catalog Compare. Which reminds me…
I absolutely love the BimlFlex Savings Calculator near the bottom of the page (click to enlarge)!
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?
DataSets, Sources, and Sinks
Pipelines and Activities (including control flow)
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!
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.
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.
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:
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:
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:
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!
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.