In my introduction to the topic of using SSIS in the cloud, I noted that SSIS is not currently available in a platform as a service (PaaS). Unlike SQL server databases and more recently SSAS, which are both available as both on-prem and PaaS services, Integration Services must be run on a user-installed instance of SQL Server. This limitation means that your SSIS package must run either in an on-premises server or in a cloud VM. In this post, I’ll address the former by loading to Azure from on-prem SSIS.
Loading to Azure from On-Prem SSIS
When loading on-premises data to an Azure relational database, the easiest solution will usually be to move the data using Integration Services running on an on-prem machine. As an aside, SSIS won’t necessarily be the best performing solution for loading Azure databases using on-prem data (especially when loading SQL Data Warehouse). I’m purposefully starting with the simplest design to ease into more complicated load scenarios later in this series.
In this scenario, moving on-prem data to an Azure relational database requires the following:
- An Azure database. I’ll be demonstrating by using a SQL Database as a destination, but the method to load a SQL Data Warehouse from SSIS would be very similar.
- On-prem data that can be moved to said database
- An on-prem instance of SQL Server configured to execute SSIS packages
Set up the Connection Manager
There aren’t any special tools required to connect to an Azure database from SSIS. Use one of the native connection managers – OLEDB, ADO.NET, or ODBC – to make a connection to SQL Database or SQL Data Warehouse. To create the connection manager, you’ll need the full name of the Azure SQL Server, which can be found in the server properties blade of the Azure portal.
You’ll need the login credentials for that server as well (unless you’re using Azure Active Directory). Also, you’ll have to add the public-facing IP address corresponding to the on-premises SSIS server to the Azure firewall inclusion list, if you haven’t already. You’ll get a helpful error message as shown below if you try to create an SSIS connection manager to the Azure database before the firewall rule is set up.
If this happens, you can use the Azure portal to configure the firewall rule to allow traffic from that server. Just keep in mind that the address to add in this rule is the internet-facing IP address (or range) used by the machine running SSIS, not the internal IP address of that machine.
Build the Load Logic
Once the connection manager to the Azure database has been created, you’ll build out the SSIS package just like any other on-prem load process. By this point, the Azure connection is just another connection, so you can use it as a data flow source or destination, or use the Execute SQL Task to run T-SQL against it. For this example, I’ll build a Data Flow task to load data from a locally-stored flat file containing aviation data into an Azure SQL Database table.
In the OLEDB destination above * (yes, I know that technically OLEDB is deprecated, but I’m going to keep driving it until the wheels fall off), I can select an existing table, or use the create table dialog box to create a new table just like an on-prem connection.
With the local flat file as the source and the Azure SQL Database table as the destination, I’ll run the package. Although it runs a bit slower than a fully on-prem load, the narrow 208,000-row set of data loads into the SQL Database in under 15 seconds.
In this example, I am loading to Azure from on-prem SSIS, using my instance of SQL Database as a destination. However, the data load could go either way. As noted, once the connection manager has been set up, SSIS treats an Azure database just like any other connection and can be used as a source, destination, or transformation. In fact, you could use on-premises SSIS to load data between two different Azure databases, but in that case you’d get better performance by running SSIS in an Azure VM (to be covered in the next post in this series).
Conclusion
Although SSIS is not yet available in a PaaS architecture, loading to Azure from on-prem SSIS is a good option for local-to-cloud load scenarios. As shown here, connecting to an Azure database is relatively simple, and works similarly on instances of both SQL Database and SQL Data Warehouse.
* Update 12/8/2017: In October of this year, Microsoft announced that they have undeprecated OLE DB, and it will continue to be a part of the data access stack.
MS made a feature pack (released at march 1st) to include SSIS in Azure. Could you please comment if this release will allow SSIS to be executed in the cloud. I’m assuming that this wasn’t available when you wrote the article.
Hi Norberto, the SSIS feature pack for Azure (https://docs.microsoft.com/en-us/sql/integration-services/azure-feature-pack-for-integration-services-ssis) is a set of tools to allow connection to various Azure services. The execution of SSIS packages must still take place on an installed instance of SQL Server, either on-prem or in a cloud VM.
Update since u wrote this Tim is oledb is no longer being deprecated…yay!
Yes, that was a great move by Microsoft! I’m adding an addendum to this post for that.
If I want the SSIS packages to be executed from the AzureVM instead of the on-prem SQL Server, without modifying firewall rules, is there any gateways or recommendations for it?
Is there any specific requirements needed for deploying a package to SSISDB and automating the package running as a scheduled SQL server job. We have a package that runs in SSIS designer but will not run successfully when deployed?
If a job runs in SSDT but does not run when executed from SQL Server Agent, it’s usually caused by one of two issues: permissions or file paths. Make sure the account under which the job executes on the server has the appropriate permissions, and use UNC paths rather than hard-coded drive letters when reading from or writing to files in SSIS packages.
Hi Tim,
We are attempting updates with this method. It’s excruciatingly slow!
Is there a way to execute a SQL command in Azure that send all updates as a bulk from the local DB?
Hi Tim, in regards to your comments on Dec 8,2018. When attempting to run the SSIS from the VM on cloud/ on-premises SQL server agent job to load data to azure sql DB/MI DB; how to give permission to the service account of the agent service on Azure DB. The VM/ on-premises SQL Server service account uses windows AD ID but the Azure SQL DB uses Azure active directory ID.