Home > SQL Azure > Upload data to SQL Azure Database using SSIS

Upload data to SQL Azure Database using SSIS

In this blog post I will be showing you how to transfer data to SQL Azure database.


Step 1: Create a package and a dd Data Flow Task

pic1Step 2: Add ADO NET source and ADO NET destination task

pic2Step 3: Configure source task with your local SQL 2012 server and select AdventureWorks2012 database.

pic3Step 4: Select table Location for data upload.


Now connect ADO NET source task with destination task


Step 5: Configure ADO NET Destination task with SQL Azure database.


  1. We cannot use OLE DB Source or Destination task to connect to SQL Azure database. So, we are using ADO NET.
  2. SQL Connection string is available in the SQL Azure portal. So, we have to use the server name given in the string.
  3. SQL Azure tables must have a primary key column with clustered index. otherwise, we cannot use that table. So, we have to create the table in SQL Azure database before we configure destination task.

Step 5.1: SQL Azure connection string.

pic6pic7Step 5.2: Create Location table in SQL Azure.

pic8SQL Query:

CREATE TABLE [Location](
[LocationID] [smallint] IDENTITY(1,1) NOT NULL,
[Name] Varchar(100) NOT NULL,
[CostRate] [smallmoney] NOT NULL,
[Availability] [decimal](8, 2) NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
[LocationID] ASC

Step 6: Configure ADO NET Destination task now

pic9Now, Select Location table in SQL Azure database SQLServerRider and map columns between source and destination tasks.


We have configured the source and destination ADO NET task.

pic11Step 7: Execute the package

pic12Package has executed successfully. I want to see the data that are transferred from my local machine to SQL Database. So, I am going to SQL Azure portal.

pic13Click Edit button and go to Data tab to see the uploaded data


We have uploaded the data from one machine to SQL Azure Database very easily using SSIS. Further read about this topic.

  1. No comments yet.
  1. January 15, 2013 at 7:21 pm
  2. January 16, 2013 at 11:46 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: