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

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

 

Abstract

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).

Abstract

  • 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.

Enjoy!

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.

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!

:{>

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):

Wait…

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:

Conclusion

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!

:{>