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.

Conclusion

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!

:{>

One Reply to “Lift and Shift SSIS Part 1: Deploy Integration Runtime SSIS”

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.