So you want to run your trusty old friend SSIS in the Azure cloud? Well you are in luck. Here I will go through the basic steps involved in running SSIS in the cloud. Basically you just need to create a SSIS Runtime Environment and deploy your SSIS Project or Packages to this Runtime Environment. To create the RT environment, we use the Data Factory Service provided by MicroSoft Azure cloud.
I think it is important to note the differences between Azure Data Factory and SSIS. SSIS is used more for transformations and Data Factory is more of an integration tool. SSIS does extract transform and load operations, where as Data Factory mainly just pulls data from a source and loads it. In short the big difference is the transformation aspects.
What we will do here:
- Setup an Azure VM server to house SSMS and SSDT (SSIS)
- Spin up an Azure SQL Database.
- Create a SSIS Run time environment in Azure DataFactory
- Deploy the SSIS project.
Setup a Azure VM server to house SSMS and SSDT (SSIS)
Let’s create a VM to house our SQL Server Management Studio (SSMS) and (if you don’t have one already) Visual studio with SQL Server Data Tools (SSDT) for SQL Server Integration Services (SSIS).
Click ‘Create a resource’ -> ‘Get started’->’Windows Server 2016 VM’.
In Step #1 of ‘Create virtual machine’ let’s enter a name. I chagned the VM disk type to HDD, so I can build out a cheaper server. Add username and passwords. Let’s create a new resource group for this POC. I called mine blog…. Creative, I know…
Click ‘Select’ at the bottom.
Step #2
Let’s select a server size. I selected A1. Trying to go as cheap as possible without blowing my brains out from slowness.
Click ‘Select’ at the bottom.
Step #3
I basically take all the defaults here. Most of this has to do with networking, which I’m not going to cover here. In the select inbound ports, I will select RDP, so that I can access this RDP from home network/machine.
Note: This option allows anyone to access that port. If you have security concerns you can lock this machine down to only be accessed from your home/work/personal network IP.
Click ‘OK’.
Step #4.
Check the summary. Click ‘Create’. FYI – This will take some time. Like 20 minutes.
After your VM is created, you can access it using the following steps.
Select ‘Dashboard’, then find the name of your VP under all resources. I called mine ‘MyServer’.
Looking at the overview screen, you can select the copy icon next to the public IP address to copy the IP to your clipboard. This is what we will use to RPD into our Windows Server.
Now use that IP (and the username/password combo from VM setup) to configure your remote desktop app.
This should log you into your instance. If you have issues, you probably do not have the RDP ports open to your IP.
Once you access to your server, let’s install the SSMS tools.
In your remote session, open MS Edge web browser. You can google (or I guess I should say Bing ‘SSMS Download’ and it will direct you) at the time of this writing the link was:
Note: You may have to go to Internet Options -> Security-> Custom level->and ‘Enable’ File downloads.
After downloading launch the SSMS-Setup MSI. And run through the standard Windows install, by clicking….You guessed it… ‘Install’.
Next, if you do not already have an Visual Studio environment, you will want to download and install that too. Or if you just want SSIS, you should download SSDT (Sql Server Data Tools). Click and install SSDT-Setup.
Note: the SSDT will take some time to install, you may want to resize your machine just for this install. You may also want to temporarily suspend defender, b/c it will eat up all your cpu trying to scan the install files.
Note: You may also need to install the SSIS Azure Feature Pack. This will give your SSIS environment added features for Azure (ie, Blob access).
While these packages install, you can move on to the next step and set up the Database. We use SSMS to connect to the database after it is set up.
Spin up an Azure SQL Database
Next up, let’s create a SQL database. Navigate to the MS Azure Dashboard. Click ‘Create a resource’ . Under ‘Azure Marketplace’ click ‘Databases’. Under ‘Featured’ click ‘SQL Database’.
Next give the Database a name and select ‘Server Configure required settings’, if you do not have a server set up ready to use. Select ‘Create a new server’.
Give the new server a name, and admin login name, password and location. Click select at the bottom of New Server section.
Notice: at the bottom in grey the box is checked for ‘Allow Azure services to access server’, this is what we want. This will allow our VM and Data Factory to access this instance easily.
Select a pricing tier, and then ‘Create’ at the bottom.
After the DB connects, you can go back to your VM and launch ‘’Microsoft SQL Server Management Studio” and connect.
After SSMS launches if it does not automatically prompt you for a connection, select the connection icon at the top left in ‘Object Explorer’.
Enter your Database credentials. You can copy your DB name from the overview screen of the SQL database. Click ‘Connect’.
Create a SSIS Run time environment in Azure DataFactory
Now for the new step. We will create a Runtime Environment for SSIS using a Data Factory. Don’t worry it’s pretty simple.
Back in our Azure Dashboard. Select ‘Create Resource’, then ‘Analytics’ (or Integration) and ‘Data Factory’ in the resulting menu.
Fill out the params for a new data factory. Basically, add a name, select your existing resource group, and the location you are currently using.
After the DF is created, go back to the Dashboard, by selecting Dashboard on the left, or ‘Home’ from the bread crumbs.
Select your Data Factory.
Select ‘Author & Monitor’.
In the resulting screen, select ‘Configure SSIS Integration Runtime’.
In the ‘Integration Runtime Setup’ screen we can take most of the defaults.
Warning: Pay Attention to Node Size. If you are doing this for your own edification, and are not worried about performance, you may want to select smaller nodes. And/or be sure to stop the Run Time Environment when you are not using it. I did neither and burned through all my free credits very quickly.
In the next screen just select the database server you created. Add the username and password you created for this server. Select ‘Test connection’.
Hopefully you will see the following screen. Notice the green check and ‘Connection Successful’ at the bottom. Click ‘Next’ at the bottom.
In the final screen we will take the defaults for advanced settings. Click ‘Finish’.
Now if you add another connection in SSMS, this time selecting the SSISDB database. After adding this you should see some familiar items in SSISDB (i.e. IS Catalog).
Add a new connection. Enter your Database details. Click options at the bottom.
Under ‘Connection Properties’ tab, select ‘SSISDB’ under user database folder. Click ‘OK’, and then Click ‘Connect’.
Notice the IS Catalog in your new connection. This is where deployed objects will go.
Deploy.
There are actually two ways to deploy. Both similar. We will go with option 1 below. I feel like it is a little more intuitive.
- From Visual Studio (SSDT).
- From SSMS by selecting the project file.
Below is the simple job I created in SSIS. I’m not really going to go into the specifics, as this post is more about creating the runtime environment. I’m basically just copying an Azure Blob object (a data file from IMDB.com) to a table in the SQL Database we just created.
In the Visual Studio Solution Explorer, right click the project you would like to deploy and select deploy. My project still has the default name of ‘Integrations Services Project1’.
Then follow the next few screens that walk you through the deployment.
Click ‘Next’ if see the introduction screen.
Enter in the database information we used for setting up our Runtime environment. Select ‘SQL Server Authentication’, and use the SQL Database server username and password you set up earlier.
Click ‘Connect’ (to the right of ‘Authentication’).
Then click ‘Browse’ to select or create a folder. Click ‘New Folder’ to create a folder.
Click ‘OK’.
Give the destination project a name. SSDT will automatically populate the path with the current project name, which is just fine. You’ll notice that mine below has the default project name (because I didn’t bother renaming in my example job).
Click ‘Next’.
Check out the review screen and click ‘Deploy’.
If you go back to SSMS, you will see your project added somewhere under the ‘Integration Services Catalogs’.
You can execute the project or individual packages. Here I am going to execute Package2.dtsx by right clicking it and selecting ….. You guessed it … ‘Execute’.
Verify the correct package is selected, and click ‘OK”.
You will be prompted to execute report. Click ‘Yes’ to open the log/report.
View the log to see if there are any errors.
Back in Azure in the Integration Runtime page (Note: this is a different web page from where the dashboard is created. A new tab was created when we selected our Data Factory and selected ‘Author and Monitor’.) Select the gauge icon to view the Integration Runtime monitor.
Note: The icon next to the status icon (two vertical bars) is what you click to stop the Integration Runtime’.
If we refresh the ‘All Executions’ report back in our SSMS window in our VM, hopefully we will see success.
A Data Guru 