Migrating SSIS Packages to SSIS Azure
In case you missed the announcement (and there were a lot of announcements during MSIgnite), SQL Server Integration Services is in Public Preview on Azure!
I’ve written about it elsewhere in greater depth, but here are the headlines:
- It makes use of SSIS Scale Out, which was released as part of SQL Server 2017.
- Although it is based on SSIS Scale Out, you can’t actually configure SSIS Scale Out to run on the instance. If this confuses you then read my in-depth post.
- SSISDB is installed in either SQL Azure or on a Managed Instance.
- You don’t have to create Integration Services Catalog/SSISDB yourself; it is done for you. So that annoying key management is no longer a problem.
- You don’t have exposure to the worker agents; you define how many you want and the deployment process does the rest.
- The aim was for it to be a “lift and shift” process – that you can take your ispac and deploy it to the Integration Services Catalog and configure the Environments exactly how you have on premise.
- You’ll need to set up a VNET to connect back to your domain (ie out of Azure.)
- Similarly, there is extra set up to connect using domain accounts.
I’ve had a play around with it and am impressed how easy it is to move a project from on premise to Azure. So with that in mind I’m going to guide you through the documentation and get WWI SSIS running in Azure. WWI SSIS is a Integration Services project sample which is available in the World Wide Importers samples. Which in turn is part of the sql-server-samples repo on GitHub. One great reason I’m using it is because it transfers data from WWI OLTP to WWI DW, which are also par of the repo. So what this means is that I’m not getting caught up in creating a V-NET to demonstrate SSIS in Azure.
This guide is something of a curated list of resources, so there is quite a few links to other resources you’ll need to take into account. But because a comprehensive, all-in-one guide would be so large, take so much time to write and proof, and would be a regurgitation of what other people have already said better elsewhere, the curated approach seemed better.
I’m going to assume a few things -
- you have an Azure Subscription
- An Azure SQL Logical Server you can deploy SSISDB to (basic tier is good enough). Ensure you have set the firewall settings to accept connections from the box you are running the script from.
- You know what Azure PowerShell is, and you have at least 4.4.0 downloaded/installed on a box that can connect to your Azure Subscription. Be warned though, it can break your scripts!
- You have Visual Studio 2017 installed with SSDT 2017 Integration Services support so that you can run Integration Services Deployment Wizard
- Have access to MSDN documentation, and read it at least once.
If you’re missing out on one or more of these things then best install/read through those (hint: click the links) or just read through and enjoy my screenshots!
1. Create SSIS Azure Objects
Happily, Microsoft have supplied the entire script for us to run to create the objects required. What this script does is -
Validate the connection to database
Log in to Azure and select subscription (entered in $SubscriptionName)
Select a resource group (named in $ResourceGroupName)
Create a data factory (named in $DataFactoryName)
Create an integration runtime (named in $AzureSSISName)
Deploy SSISDB (so in $SSISDBServerEndpoint you need to specify your server)
Start integration runtime
The whole concept of deploying to SSIS Azure is something we will cover later.
There are options to scale up/scale out your nodes etc, but as we’re deploying approximately one package to run once as a demo, we can leave everything else as default. The process takes roughly 20 minutes, so time to go make yourself a cup of tea.
here is a copy of the script required
After you’ve come back from making yourself a tea, the process should nearly be complete. During this time, you will want to have downloaded or cloned sql-server-samples and restored the WWI OLTP and WWI DW backups (ie,the bacpacs) to the same Azure SQL Logical Server you specified in the script above. Follow this incredibly easy guide on how to restore them to your logical server.
This will also take some time (ie enough time to go make another tea, and if you’re on the Basic tier, enough to drink it and make another one), and do not be concerned if you see the message below -
If all is right in the world with the bacpac restores and the PowerShell script you will have the two databases restored on the same server as the newly created SSISDB. You can connect to the server using SSMS to see the new Integration Services Catalog. Crucially, you need to specify SSISDB in the connection options…
to see the Integration Services Catalog though. If you don’t you’ll just see SSISDB and no catalog! See below to spot the difference.
At this point, if you want to know a bit more about the internals of SSIS in Azure, read up my other post that covers the internals in greater depth.
Ok, so, we’ve got a SSISDB, got a Data Factory, now we need some packages. At this point, all we’re going to do is take the ispac from the releases on GitHub and install onto the server. We’ll make some manual changes to the connection managers on the server and run the dtsx package. Automating deployments can be achieved with using AssistDeploy. Follow this post on how to get started, as you’ll definitely want to parameterise the connection managers.
Create a folder on the server, and right-click the “Projects” folder and select “Deploy Project…”
if you’ve ever done this for on-prem, you’ll find this very familiar…
eventually you’ll get it deployed. Hurray!
Now we have the project deployed, we need to make a change to the connection managers. For both of the connection managers in the project, we w ill need to edit the value for ServerName, UserName and Password to that of the Azure Logical Server.
The final change we need to make is to remove Integrated Security from the connection string. Edit theconnection string and Remove "Integrated Securtiy SSPI;"
With these changes made, we should be able to run the package. So expand out project and execute the package.
The package takes about 5 minutes, so maybe enough time to make a tea? At any rate, once it is run we have now executed our first SSIS package in Azure!
it’s actually pretty straightforward to take a SSIS project and get it deployed to SSIS Azure, and the familiarity with the process via on-prem definitely helps. As more and more teams migrate their SSIS packages to Azure, it will be interesting to see that works well and not so well at a package level. But with this simple demo, the lift and shift concept certainly worked!