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.