Enterprise data can be of very different kinds ranging from flat files to data stored in relational databases with the recent trend of storing data in XML data sources. The extraordinary number of database related products, and their historic evolution, makes this task exacting. The entry of cloud computing has turned this into one of the hottest areas as SSIS has been one of the methods indicated for bringing ground based data to cloud storage in SQL Azure, the next milestone in Microsoft Data Management. The reader may review my book on this site, “Beginners Guide to Microsoft SQL Server Integration Services” to get a jump start on learning this important product from Microsoft.

SQL Azure

SQL Azure is one of the three pillars of Microsoft’s Azure cloud computing platform. It is a relational database built on SQL Server Technologies maintained on Microsoft’s physical site where subscribers like you and me can rent out storage of data. Since the access is over the internet it is deemed to be in the cloud and Microsoft would provide all data maintenance. Some of the key benefits of this ‘Database usage as a service’ are:

• Manageability
• High Availability
• Scalability

Which in other words means taking away a lot headache from you like worrying about hardware and software (SQL Azure Provisioning takes care of this), replication, DBAs with attitudes etc.

Preparation for this tutorial

You need some preparation to work with this tutorial. You must have a SQL Server 2008 installed to start with. You also need to register yourself with Microsoft to get an invitation to use SQL Azure by registering for the SQL Azure CTP. Getting permission is not immediate and may take days. After you register agreeing to the license terms, you get the permission (You become a subscriber to the service) to use the Azure Platform components (SQL Azure is one of them). After subscribing you can create a database on the SQL Azure instance. You will be the administrator of your instance (Your login will be known as the server level principal equivalent to the landbased sa login), and you can web access the server with a specific connection string provided to you and a strong password which you create. When you access the Azure URL, you provide the authentication to get connected to your instance of the server by signing in. Therein, you can create a database or delete an existing database. You have couple of tools available to work with this product. Read the blog post mentioned in the summary.

Overview of this tutorial

In this tutorial you will be using MS SQL Server Integration Services to create a package that can transfer a table from SQL Server 2008 to SQL Azure for which you have established your credentials. In my case the credentials are:

• Server: tcp:XXXXXX.ctp.database.windows.net
• User ID: YYYYY
• Password: ZZZZZ
• Database: PPPPPP
• Trusted_Connection=False;

Here XXXXXX, YYYY,ZZZZZ, and PPPPPP are all the author’s personal authentication values and you would get yours when you register as previously mentioned.

Table to be migrated on SQL Server 2008

The table to be migrated on the SQL Server 2008 (Enterprise server, evaluation edition is shown in the next figure). PrincetonTemp is a simple table in the TestNorthwind database on the default instance of the local server on a Windows XP machine, with a few columns and no primary key.

Create a SQL Server Integration Services Package

Open BIDS (a Visual Studio add-in extending support to build database applications with SQL Server) and create a new SQL Server Integration Services project[Use File |New |Project…in the IDE]. Herein the Visual Studio 2008 with SP1 is used. You need to provide a name which for this project is GroundToCloud. The program creates the project for you which you can see in the Solution Explorer. By default it creates a package for you, Package.dtsx. You may rename the package (herein ToAzure.dtsx)and the project folders and file appear as shown.

Add an ADO.NET Source component

Drag and drop a Data Flow Task to the tabbed page Control Flow in the package designer. Into the Data flow tabbed page drag and drop an ADO.NET Source component from the Toolbox. Double click the component you just added, from the pop-up menu choose Edit… The ADO.NET Source editor gets displayed. If there are previously configured connections one of them may show up in this window. We will be creating a new connection and therefore click the New… button to display an empty Configure ADO.NET Connection Manager as shown (again, if there are existing connections they all will show up in this window). A connection is needed in connecting to a source outside the IDE.

Double click the New… button to display the Connection Manager window which is all but empty. Fill in the details for your instance of ground based server as shown (the ones shown are for this article at the author’s site). You may test the connection by hitting the Test Connection button.

Clicking the OK buttons on the Connection Manager and the Configure ADO.NET Connection Manager will bring you back to the ADO.NET Source Editor displaying the connection you have just made as shown. A connection string also gets added to the bottom pane of the package designer as well as to the Configure ADO.NET Connection Manager.

Click on the drop-down and pick the table (PrincetonTemp) that needs to be migrated to the cloud based server, SQL Azure. Click OK. The Columns navigation on the left would reveal all the columns in the table if it were to be clicked. The Preview button would return the data returned by a SELECT query on the columns as shown.