SSIS Package Execution In Azure Is Now Available

Well, it’s been some time coming but SSIS packages are the latest product to make the move from on premise to Azure. You can now take your SSIS projects and deploy them to the new Platform as a Service (PaaS) offering in Azure.

The aim of the team at Microsoft was for users to take their current SSIS packages and just “lift and shift” these to Azure. So in development terms that means that there are minimum to no changes to be made in the solution at least.

But before we get into the deployment and running of SSIS packages in a further post, let’s take a brief look at SSIS package execution in Azure.

All you need to install SSIS In Azure is a Storage Account/Key for logging to be saved to, and a SQL Azure Server set up so that the PowerShell can install the Integration Services Catalog (ISC) on the instance. Yes, that’s right, you host a copy of SSISDB. And the good thing is that you don’t have to worry about saving the key like you do when you create an ISC on premise. So one less task for the DBA. The other neat thing is that because you have access to SSISDB/ISC, you can view everything you need through SSMS, just like an on-prem install of SSIS.

Look at the screenshot below and you’ll notice that I am connected to a local instance running ISC, as well as a SQL Azure instance.

But notice the difference between the two connections to the same SQL Azure instance? Let’s zoom in a bit to make it obvious:

In one connection I can see ISC and not the user databases. But in the other I only see user databases. What’s the difference? The difference is when I make the initial connection through SSMS, if I want to see the ISC then I need to set SSISDB as the default database.

This behaviour is not the same on-prem, but it’s important to know this distinction, elsewise you may be wondering why after setting up SSIS In Azure you cannot see the ISC.

So during set up you will have needed to have defined how many nodes you wanted and what size they were, plus the level of parallelisation. But what of this? Where does this fit in?

If you query the view catalog.worker_agents you will see that there are entries there: one for each of the agents you defined when you configured SSIS In Azure. I wanted 4 agents, and low and behold I have 4 worker_agents:

But what even are worker_agents? Is this something pertinent to SSIS In Azure? In fact, no! Although they are a new feature, released along with SQL Server 2017. To quote the blurb: “Integration Services Scale Out provides high performance package execution by distributing executions to multiple machines. You can submit a request for multiple package executions in SQL Server Management Studio. These packages will be executed in parallel, in a scale out mode.”

Good stuff!

So what SSIS In Azure is a sort of implementation of SSIS Scale Out at Package level in Azure. And I refer you to take note of the emphatic formatting in the previous sentence: If you don’t have time to read all the documentation for SSIS Scale Out, I’ll save you some time and tell you that you can’t configure the Scale Out on the SSIS In Azure instance like you can on the on-prem install. Having determined that what much of the configuration is out of our hands, which is a good thing: all we care about is that our SSIS packages are executed, not where. I’ve read over the SSIS Scale Out documentation, and there’s a lot to take in, lots to manage, and generally adding another thing to manage is not welcome.  To the end user, SSIS In Azure is identical to SSIS on-prem install. If we wish to scale up, we improve the size of the nodes (sizes supported are Standard_A4_v2, Standard_A8_v2, Standard_D1_v2, Standard_D2_v2, Standard_D3_v2, Standard_D4_v2), and if we want to scale out we just increase the number of nodes (from 1 to 10) or the number of parallel executions on any one node (1 – 8 per node). This is far easier than having SSIS installed on-prem. Even as a VM, it’s impractical to scale up/out so easily on-prem as it is with SSIS In Azure.

OK, so that’s a bit of the background covered, now how about actually running something on SSIS In Azure? Is lifting and shifting a workload really that easy? Well short answer is yes, slightly longer answer is yes and no, and the full answer which explains these is below…

Regarding the short answer: if you have a workload that exists purely in Azure then yes, you can deploy your project directly to SSIS In Azure and expect it to work. Regarding the slightly longer answer: if you need to access on-prem data, you will need to configure a Classic VNET and host SSIS In Azure in that. There is also an option to access on-prem data with Windows Integration.

Tune in next time, when we deploy a SSIS project and get it running on SQL In Azure, with successful results!