In SSIS Data Flow let us create an OLE Source and OLE destination and two OLE connections one to SQL Server and the other one to SQL Azure.
The OLE connection to SQL Azure will have the server name and SQL Connection to the server
Since I don’t have a destination table at this time I will simply click on New and change the destination table name to tContacts
Click Ok to create the table on the SQL Azure
Click on mapping to check the mapping between the source and destination tables.
Now let’s execute the package. Oops it failed so what happen
Taking a close look to the execution progress indicate
[DST – SQL Azure [2]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: “Microsoft SQL Server Native Client 11.0” Hresult: 0x80004005 Description: “Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again.”.
It is because all SQL Azure tables require a clustered index and we don’t have one for our new table. Now let’s go back to management portal and create a crusted index for the table.
Perfect our clustered index is created on ID column
Now that we created the index on the server let us execute the ETL package once again.
Perfect I was able to execute the package the package and transfer around 350 thousand records from a local SQL Server to SQL Azure in about 2 minutes and 22 seconds with connection speed of 27.68 Mbps download and 5.6 Mbps upload speed.
Downloading data from SQL Azure could be done with the same way by converting the source and destination.
