SSIS in SSMS Version 18: Try SSIS in Azure Data Factory

There’s a new option when you right-click the Integration Services Catalogs node in SSMS v18: “Try SSIS in Azure Data Factory”:

Clicking the “Try SSIS in Azure Data Factory” option in SSMS v18 opens the (new) Integration Runtime Creation Wizard (ISIRCreationWizard.exe, which is found in the <installation drive>:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\CommonExtensions\Microsoft\SSIS\150\Binn directory):

Lift and Shift SSIS to Azure-SSIS

Before you can lift and shift SSIS to Azure-SSIS, you need an SSIS Catalog in the cloud at the time of this writing. The first page of the wizard – “SSIS in ADF Introduction” – includes links to:

SSIS in ADF Configuration

The next page in the wizard is labeled ” SSIS in ADF Configuration”. After logging into your Azure account, you may configure:

  • An Azure Subscription
  • Catalog Server Endpoint – an instance of Azure SQL DB or Azure SQL Server Managed Instance
  • Connection credentials
(click to enlarge)

I can hear you thinking, “Why the squirrel-y-looking user name, Andy?” See my post titled Andy’s Database Credentials Hack.

After clicking the Connect button, you should see an Information message declaring: “Connection is successful.”

If you don’t see the “Connection is successful” message, you will likely see a message similar to that shown below:

Oops

Click the Create button. The Integration Runtime Creation screen displays and reports progress as the wizard:

  • Creates the Resource Group
  • Creates the Azure Data Factory
  • Creates the integration Runtime

I confess: I am the curious type. When I saw this list, I had some questions. So I logged into portal.azure.com and poked around a bit.

SETTINGS

The wizard created an Azure Data Factory and a resource group based on the name of the server I used, plus a formatted timestamp string:

The wizard also created an instance of Azure-SSIS on the server I specified. One interesting note? Azure-SSIS is created but not started.

At the time of this writing, creating an instance of Azure-SSIS from the Azure Portal automatically starts the instance. I think I might like the option to start the Azure-SSIS instance manually from the portal – with maybe a “Start after created” checkbox.

Clicking the edit icon revealed the configuration SSMS v18 chose for me:

I’m pretty sure these are the defaults when one creates Azure-SSIS from the Azure Portal. Interesting to note: The “Already have a SQL Server licence?” option is set to Yes, which is not the default. This makes sense. I’m creating the instance of Azure-SSIS from SSMS v18.

Returning to the Wizard:

Clicking the Next button opens the adf.azure.com Azure Data Factory Authoring page and displays the Azure-SSIS Integration Runtime Setup blade:

Clicking the Start button begins starting the Azure-SSIS Integration Runtime:

The Azure-SSIS Integration Runtime starts in about five (5) minutes, which is an awesome improvement over the 20-30 minute startup times of the past!

The final step in the Integration Runtime Creation Wizard is to click the Close button:

One Last Thing…

When you right-click the SSMS v18 Integration Services Catalogs node in Object Explorer and then click Create Catalog, the Catalog Creation Wizard starts:

The Catalog Creation Wizard includes a new “Create SSIS IR” button:

When clicked, this button starts the Integration Runtime Creation Wizard.

Conclusion

Well played, Azure-SSIS Team. 
Well played.

Need Help Lifting and Shifting SSIS?

Enterprise Data & Analytics specializes in lifting and shifting SSIS from on-premises to Azure.

Please contact Enterprise Data & Analytics to learn more.

Andy Leonard

andyleonard.blog

Christian, husband, dad, grandpa, Data Philosopher, Data Engineer, SSIS and Biml guy. I was cloud before cloud was cool, :{>

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.