In our most recent article published on this forum, we have presented a procedure for uploading data to a Windows Azure SQL Database from an on-premise SQL Server instance that leveraged BACPAC formatted files, while operating under the assumption that our on-premise data store is already cloud-compliant. Now we will describe an alternative method of accomplishing the same objective (in the same circumstances) by employing a more traditional approach to data extraction, transformation, and loading (ETL), which relies on SQL Server Integration Services (SSIS).
There is a number of restrictions that need to be considered when applying SSIS-based technologies to Widows Azure SQL Databases, due to the inherent limitations of the latter (for their comprehensive list, refer to Windows Azure Online Library). For example, lack of support for OLE DB in the Microsoft Platform as a Service (PaaS) offering implies that you have to rely on ODBC or ADO.NET when defining SSIS connection managers intended for interaction with SQL Database sources or destinations (which we will explore in more detail shortly). Similarly, you need to ensure that the schema of SQL Databases complies with all relevant Windows Azure PaaS restrictions, such as, the presence of clustered indexes or the ROWGUIDCOL and NOT FOR REPLICATION options.
We will demonstrate the relevant SSIS functionality by uploading content of the SalesLT.Customer table from an on-premise copy of AdventureWorks2012 database (available for download from the CodePlex Web Site) to its Windows Azure PaaS equivalent. For the sake of simplicity, we will take advantage of the procedure for creating the SQL Database compliant AdventureWorks2012 database schema that we have described in detail in the Migrating SQL Server Database to Windows Azure – Resolving Incompatibility Issues article published on this forum.
To complete this process (as well as to subsequently create our sample SSIS package), you will need SQL Server Data Tools – Business Intelligence for Visual Studio 2012, which you can download from the Microsoft Download Center. First, use it to publish the Visual Studio project containing the Azure PaaS compatible representation of the database schema to a new SQL Database (which we will name AdventureWorks2012_SSIS) that will be the target of our SSIS-based data load (you will find the procedure guiding you through this process in another of our articles titled Migrating SQL Server Database to Windows Azure – Deploying SQL Database). Once the target database schema is populated, we are ready to proceed with the creation of our sample SSIS package.
Start by selecting the New Project entry from the File menu. In the New Project dialog box, after navigating to the Integration Services subnode of the Business Intelligence node of the Templates section, you should be able to find the Integration Services Project. Use it to create a new Visual Studio project with an empty package file named Package.dtsx. This will automatically adjust the interface by exposing all relevant SQL Server Intergration Services windows. In the Design pane of Package.dtsx (the main area of the Data Tools window), switch to the Data Flow tab and click on the link in its center labeled No Data Flow tasks have been added to this package. Click here to add a new Data Flow task. As indicated, this will generate a new Data Flow task and automatically display the Connection Managers window underneath. Right click within that window and select an appropriate option from the context sensitive menu to add a new connection manager to the SSIS package. To connect to the local, on-premise database, you can choose either New ADO.NET Connection, New OLE DB Connection, or the ODBC Connection (we will use the first of them). In the Connection Manager window, type in the on-premise server name, specify the appropriate authentication method, and pick the AdventureWorks2012 database from the listbox.
Next, you need to establish a connection to data store in Windows Azure. As we mentioned earlier, in this case, your options do not include OLE DB, since this approach is not supported when dealing with SQL Databases. This leaves you with the following choices:
- ADO.NET connection – this involves specifying the SQL Azure server name, providing SQL Server Authentication credentials, and choosing the target SQL Database name within the Connection Manager window.
- ODBC connection – in this case, you need to provide a connection string in the format
Driver={SQL Server Native Client 11.0};Server=tcp:SQLServerName.database.windows.net;Database=SQLDatabaseName;Uid=SQLLogin;Pwd=SQLPassword;, where SQLServerName, SQLDatabaseName, SQLLogin, and SQLPassword represent the target server, database, and SQL Database credentials, respectively. Note that both ADO.NET and ODBC connection strings are readily available from Windows Azure Management Portal by clicking on the Show connection strings link in the Dashboard of SQL Database.
In order for connection managers to work properly, you will also need to ensure that Windows Azure SQL Database Firewall allows connectivity from the public IP addresses representing your internal SSIS systems. You can identify whether this is actually the case by connecting to the Windows Azure Management Portal, navigating to the SQL Databases node, switching to the Servers section, selecting the target server in the main window pane, and displaying its Configure tab. You will find there a list of allowed IP addresses, each containing an IP address range and a timestamp indicating when the corresponding firewall change has been applied. Without a valid configuration, you will encounter a connectivity error when executing the package. Such issues can also be easily identified while designing the SSIS package by clicking on the Test Connection button available within the Connection Manager dialog box. In such cases, you will be presented with an error message similar to the following:
Cannot open server 'SQLServerName' requested by the login. Client with IP address 'YourIPAddress' is not allowed access the server. To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect. Login failed for user SQLLogin This session has been assigned a tracing ID of 'GUID'. Provide this tracing ID to customer support when you need assistance.
Rather than modifying the allowed IP addresses list manually, you can also apply the change directly from the SQL Server page of the Windows Azure Management Portal (assuming that you launched your browser from the same system that hosts the SSIS package). When navigating to it, you should be prompted whether you want to add your current IP address to the existing firewall rules in order to allow management of the target server. Accepting the prompt will automatically take care of the required change.
With the updated firewall configuration in place, let’s return to our sample package. For the sake of simplicity, we will limit its content to two tasks – ADO.NET source and ADO.NET destination. As we stated earlier, we will also assume that we want to limit its scope to uploading the content of the SalesLT.Customer table from the on-premise database to Windows Azure.
To accomplish this, in the SSIS Toolbox window (located by default on the left hand side of the SQL Server Data Tools interface), navigate to the Other Sources node, expand it, locate the ADO NET Source icon, and drag it onto the package designer surface. From the ADO NET Source component’s context sensitive menu, select the Edit entry. In the Connection Manager section of the ADO.NET Source Editor dialog box, pick the ADO.NET Connection Manager pointing to the on-premise instance of AdventureWorks2012 database. In the Data access mode listbox, leave the default Table or view item and in the Name of the table or the view select the “SalesLT”.”Customer” entry. Click on the OK command button to finalize your changes.
Next, expand the Other Destinations node in the SSIS Toolbox, locate the ADO Destination icon, and drag it directly underneath the ADO NET Source we just configured. Connect the two items by extending the arrow originating from the upper one until it touches the top of the one below. Display its ADO.NET Destination Editor (by selecting the Edit item in its context sensitive menu). In the Connection Manager section, select our Azure connection with the matching entry (i.e. “SalesLT”.”Customer”) in the Use a table or view listbox.
At this point, you are ready to initiate the upload by clicking on the Start button in the toolbar, invoking Start without Debugging item from the Debug top level menu, or simply pressing the Ctrl and F5 key combination. The SQL Data Tools should automatically display the progress of the package execution, with 847 rows successfully inserted into our SQL Database. Obviously this sample scenario is very straightforward. In our upcoming articles, we will look into more involved examples demonstrating the use of SSIS when dealing with the Windows Azure PaaS offering.
