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
Now connect ADO NET source task with destination task
Step 5: Configure ADO NET Destination task with SQL Azure database.
- We cannot use OLE DB Source or Destination task to connect to SQL Azure database. So, we are using ADO NET.
- SQL Connection string is available in the SQL Azure portal. So, we have to use the server name given in the string.
- 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.
[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
)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
We have configured the source and destination ADO NET task.
We have uploaded the data from one machine to SQL Azure Database very easily using SSIS. Further read about this topic.