Introducing SSIS in the Cloud

Microsoft announced in September 2017, a preview of new Azure Data Factory V2 capabilities, including the ability to run SSIS packages in the cloud.

I presented a webinar last week on how to get started with these new features, including how to deploy SSIS in the cloud.  A recording of the presentation is available here.

The slides I presented are available here.

This post includes answers to questions posted during the live session.

I should start with a product update.  I demonstrated in the webinar how to use PowerShell to provision and manage the Azure SSIS runtime.  At the time of my talk, there was no UI to perform these operations.

A few hours after the webinar, however, Microsoft announced a preview of a UI to do most everything I demonstrated with PowerShell.  This UI provides the ability to create the Azure SSIS runtime, edit the runtime after deployment, and shut down/restart the runtime.  

The UI can be accessed by browsing your V2 Azure Data Factory, and pressing “Author & Monitor.”

This opens the page below.  Clicking the “Configure SSIS Integration Runtime” button invokes the “Integration Runtime Setup.”

The following are answers to questions posted during the webinar:

1. Does adding more nodes to the SSIS Runtime change the pricing?

Yes. The Azure SSIS Runtime pricing guide lists the price for a single node.  However, you will be charged for each node in your SSIS Runtime.

2. Can SSISDB be hosted on an Azure VM or does it have to be on Azure SQL DB?

SSIS DB must be hosted on Azure SQL DB. This is more apparent with the new UI for setting up an Azure SSIS Runtime.  See the screenshot below and the note for “Catalog Database Server Endpoint.”

3. Can you still use environment variables? This was not covered in the demo.

Yes.  All environment, variable, parameter, and connection configuration options remain the same with Azure SSIS Runtime.

4. Can you use AD integration with SSIS in the cloud? 

Yes. This post from Microsoft should be able to help you set this up.  Connect to on-premises data sources and Azure file shares with Windows Authentication.

5. Are the execution logging reports still available in the catalog in the cloud version?

Yes. These are still available from SQL Server Management Studio by right clicking a folder/project/package, and selecting “Reports.”

6. Will 3rd party tasks/transforms be supported at some point?

Yes. We understand this support is coming, but no date has been provided.  As of this post, 3rd party tasks/transforms are not supported.

7. What versions of .NET framework are supported?

Thanks to Mark Kromer for his input on this question.  The VMs spin-up Windows Server 2016 with SSIS 2017, so it has .NET versions up to 4.6.2.

8. Can I use a .NET assembly to read data from a web page?

There is currently no support for custom .NET assemblies.  If you are using a script task or script component, and your code does not use custom assemblies, these should work.

9  Do we have access to the SSIS configuration file (ISServerExec.config)?

No. There is currently no way to access the server/VM running the SSIS Runtime.

10.  Can we connect to remote FTP server to download data like we can do on-prem?

The FTP task is supported.  However, if you download files to a local or networked file server, you will need to use the Azure SSIS Runtime within a virtual network (VNET).

11. Can I schedule and execute a package via Azure Automation and PowerShell runbook? If so, can I turn on and off SSIS via PowerShell such that I’m only being charged for the time that I run the package?

Yes. For package execution, you may try running the SSIS execution procedures via the sp_executesql procedure.  Azure Automation can execute stored procedures. Regarding turning the runtime on/off, Azure Automation can do anything that can be done with PowerShell.

12. Why does it take so long to restart the SSIS runtime?

The VMs used for your SSIS Runtime are not dedicated resources. Each time the SSIS Runtime is restarted, new VMs are provisioned and then started up.

The new UI makes this clear when stopping the SSIS runtime.

13. Does the runtime offer 32-bit mode execution of SSIS packages?

Yes. Running in 32-bit mode is an option when preparing for execution, similar to how this has worked in the past. See the screenshot below.

14. Can you deploy packages from Visual Studio 2017 instead of .ispac files to Azure?

Yes. To be clear, though, Visual Studio deployment does use the .ispac file, but saves you the step of browsing/finding the .ispac file.

15.  You still have to create the packages with Visual Studio/SQL Server Data Tools. You can’t create the packages on the cloud itself, correct?

That is correct, yes. The Azure SSIS runtime does not change how packages are created.

16.  Can you further explain the NodeNumber property?

Microsoft posted this article about managing Azure SSIS Runtime performance.  From the article,

“AzureSSISNodeNumber adjusts the scalability of the integration runtime. The throughput of the integration runtime is proportional to the AzureSSISNodeNumber. Set the AzureSSISNodeNumber to a small value at first, monitor the throughput of the integration runtime, then adjust the value for your scenario.”

There is currently no way to explicitly run packages across servers in scale out mode.  In my experience, packages executed from the Execute Package Task are often run on a node other than the node executing the calling (i.e. master) package.

17.  Is the Azure Feature Pack for SSIS supported currently?

I haven’t found this support listed explicitly.  However, I’ve successfully executed SSIS 2017 packages with the Azure Blob Source component from the Azure Feature Pack.

18.  Will this work along with the Team Foundation Server(TFS)?

Yes. The development and source control management experience does not change with Azure SSIS Runtime.

Pragmatic Works offers free webinars every Tuesday at 11:00 EST. Visit our website to view the upcoming schedule or enroll. Looking to migrate to the cloud this year, but not sure where to begin? We've helped many of our over 7,000 clients make the move with our Consulting Services. Contact us today to learn more and get your cloud migration plans off the ground.