Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Read and write data from SQL Azure using SQL Server Integration Services 2008 R2


By:   |   Last Updated: 2010-02-08   |   Comments (1)   |   Related Tips: More > Azure

Problem

SQL Azure and cloud computing brings a new paradigm of database development and implementation strategy. With these changes, all of the dependent technologies also have to start adapting to support this new paradigm. SSIS is one of the technologies that would be used to move data in and out of SQL Azure in any Microsoft Business Intelligence (BI) solution built on SQL Azure. In this tip, we would discuss how to use SQL Server Integration Services 2008 R2 to read and write data from SQL Azure.

Solution

SQL Azure can be seen as a light version of SQL Server on the Windows Azure cloud computing platform. To quickly come up to speed on how to create your account and database on SQL Azure, please read this tip which should bring you up to speed to get started for this tip. Please keep in mind that all the components and technologies discussed in this article uses SQL Server 2008 R2 (Nov CTP) version, so we would go with the assumption that SQL Server 2008 R2 Nov CTP (which is the latest release as of the draft of this article) is already installed on the development machine.

SQL Server Management Studio (SSMS) 2008 R2 has a fair amount of support for SQL Azure. To learn how to get started with the this topic, please read this tip. I am not mentioning these steps again, as they are already available in the tips suggested above and to keep focus on the main subject of this tip.

Create a database called AdventureWorks on your SQL Azure instance. Using SSMS connect to your account and open a new query window for your AdventureWorks database. Use the script shown below to create a table and insert some test records.

microsoft sql server management studio

ADO.NET adapters have the required support to read and write in SSIS packages to SQL Azure instances. There are almost no enhancements for SSIS in the R2 version with the exception of a hidden gem in the ADO.Net Destination Adapter. It provides an option to Bulk Load data specifically for SQL Azure.

Follow the steps below to create a package that would read and write from the ContactDetails table on the SQL Azure instance.

1) Create an ADO.NET connection for the SQL Azure instance:

  • Create a new SSIS project, and add a Data Flow Task to your package.
  • Edit the Data Flow, and create a new ADO.NET connection.
  • Edit this connection, specify your server name which should be in servername.database.windows.net format.
  • Select SQL Authentication. Type in your credentials to access the SQL Azure instance.
  • Type in the name of the database as "AdventureWorks". Keep in mind that even if you test your connection and though it may be successful, you would not find the database drop-down populated with the name of databases.
connection manager

2) Add ADO.NET Source and Destination Adapters

  • Add a ADO.NET Source Adapter to read from SQL Azure ContactDetails table.
  • Add a Derived column to modify the record.
  • Add a ADO.NET Destination Adapter to connect to the table, and insert the modified record.
  • Configure the above mentioned transforms as show in the figures below.
package desing

source editor

derived column transformation editor

destination editor

Execute the package. If the SSIS package is configured as explained above, the package should execute successfully and insert a record into ContactDetails table. Now go back to SSMS and reconnect to Object Explorer even if your SSMS window was open and connected before you developed this package. As a point of reference, if any connection is held for a long time (approx. anything more than 5 minutes), SQL Azure will terminate the connection. Check the records in the ContactDetails table and you should be able to see the record that we inserted into this table using our SSIS package.

microsoft sql server management studio
Next Steps


Last Updated: 2010-02-08


get scripts

next tip button



About the author
MSSQLTips author Siddharth Mehta Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, January 03, 2013 - 5:18:42 AM - Rashmi Back To Top

Hi Siddharth,

Can we use oledb source to fetch data from SQL Azure source in SSIS?

 

Best Regards,

Rashmi.


Learn more about SQL Server tools