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.

Implementation

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.

pic4

Now connect ADO NET source task with destination task

pic5

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

Note:

  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,
CONSTRAINT [PK_Location_LocationID] PRIMARY KEY CLUSTERED
(
[LocationID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
 

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.

pic10

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

pic14

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

About these ads

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

Follow

Get every new post delivered to your Inbox.

Join 304 other followers

%d bloggers like this: