Running SSIS in the Azure cloud

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:

  1. Setup an Azure VM server to house SSMS and SSDT (SSIS)
  2. Spin up an Azure SQL Database.
  3. Create a SSIS Run time environment in Azure DataFactory
  4. 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’.

azure create resource

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.

azure vm step 1

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.

azure step 2 select size

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’.
azure - step 3 settings

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’.
azure - click 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.

azure - copy vm public IP

Now use that IP (and the username/password combo from VM setup) to configure your remote desktop app.

azure - config remote desktop

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.

azure - internet options custom level

azure - edge enable downloads

After downloading launch the SSMS-Setup MSI.  And run through the standard Windows install, by clicking….You guessed it… ‘Install’.

Azure - install SSMS on vm
azure - install SSMS on vm 2

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’.  

azure - car - db - sql database

azure sql database setup values

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’.

azure sql database click create 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.

azure sql db new server

Select a pricing tier, and then ‘Create’ at the bottom.

azure - sql database setup create

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’.

SSMS - connect object explorer

Enter your Database credentials.  You can copy your DB name from the overview screen of the SQL database.  Click ‘Connect’.
azure - ssms connect db

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.

azure - create resource data factory


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.

azure new data factory params

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’.

azure - author data factory

In the resulting screen, select ‘Configure SSIS Integration Runtime’.

azure -df config SSIS IR

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.

azure - df config SSIS IR 2

In the next screen just select the database server you created.  Add the username and password you created for this server. Select ‘Test connection’.

azure df cofig SSIS IR 3

Hopefully you will see the following screen.  Notice the green check and ‘Connection Successful’ at the bottom.  Click ‘Next’ at the bottom.

azure config df connection successful

In the final screen we will take the defaults for advanced settings.  Click ‘Finish’.

azure - df ssis ir 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.

azure - ssms click to connect

Under ‘Connection Properties’ tab, select ‘SSISDB’ under user database folder. Click ‘OK’, and then Click ‘Connect’.

azure - ssms connect to ssisdm

Notice the IS Catalog in your new connection.  This is where deployed objects will go.

azure - notice SSISDB and IS cat


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.

  1. From Visual Studio (SSDT).
  2. 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 to a table in the SQL Database we just created.

azure - SSIS job.png

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’.

azure - ssis project deploy select

Then follow the next few screens that walk you through the deployment.

Click ‘Next’ if see the introduction screen.

azure - ssis project deploy screen 1

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’).

azure - ssis project deploy screen dest1

Then click ‘Browse’ to select or create a folder.  Click ‘New Folder’ to create a folder.

azure - ssis pd select dest folder

Click ‘OK’.

azure - ssis dp folder add 2

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’.
azure - ssis pd select dest next

Check out the review screen and click ‘Deploy’.

azure - ssis project deploy screen review

azure - deploy wizzard


If you go back to SSMS, you will see your project added somewhere under the ‘Integration Services Catalogs’.

azure - view deployed packages2

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’.
azure -click execute package

Verify the correct package is selected, and click ‘OK”.

azure -exec package screen

You will be prompted to execute report.  Click ‘Yes’ to open the log/report.  

azure - view exec report

View the log to see if there are any errors.

azure - view exec report2

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’.

azure - select monitor DF IR

azure monitor DF IR.png

If we refresh the ‘All Executions’ report back in our SSMS window in our VM, hopefully we will see success.

azure - ssis success report