One of the excellent option to perform data migration from SQL server to SQL Azure is to use SQL server Integration services. And guess what, SSIS can do lot more than the just BCPing data from SQL server to SQL Azure. But SQL Azure does not provide SSIS out of the box – so we have to run SSIS locally and connect to SQL Azure DB.

In this article, we will see how to migrate data from SQL server to SQL Azure by executing a SSIS package locally.

1. Open Business Intelligence Development studio (BIDS). On my machine, I have SSIS enabled for a SQL server 2008 R2 instance and I will be creating an SSIS package on BIDS 2008. After, opening up BIDS, go to New project –>  Integration services project. configure the project name, location and then click on OK

2. Now, you will see the SSIS designer:

Rename the package name to something relevant and then drag a ‘Data flow task’ to SSIS designer

3. Now right click on the ‘Data Flow Task’ and click on edit:

4. Now drag ‘ADO.NET Source from Toolbox –> Data flow sources to the designer.

Also drag ‘ADO.Net destination’ from Toolbox –> Data flow destinations to the designer.

Now right on the ‘ADO.NET source’ and click on edit to add the SQL server connection information:

5. Furnish the information required to connect to local database. In my case, I will be migrating the data from the “saleslt.customer” of “AdventureWorksAzureLT2008” database to a table (having similar table design) of a SQL Azure database.

6.  Now drag the output of the ‘ADO.Net source’ to the input of the ‘ADO.Net destination’.

7. Now, click on edit after right clicking the ‘ADO.Net destination’

8.  Now Let’s configure the destination. In our case, the destination is a SQL Azure database

Here are the steps:

Enter credentials required to connect to SQL Azure database and then test connection.  

9. After successfully connecting to SQL Azure database, select the destination table:

10 Now click on Mappings.

In my case, they seem appropriate and so I am going to click on 'OK

11 so now we have configured the source and destination and it’s time to run the package. press F5!

In successful completion, you will see:

Go to Debug –> stop debugging to stop package execution.

Done! That’s it! we have successfully migrated data from SQL server to SQL Azure using SSIS!

Note:

1) Do not select ‘OLE DB Destination’ as the Data flow destination because connecting to SQL Azure via OLEDB is not supported, as of now

2) SSIS is not a recommended option to migrate SQL server database objects to SQL Azure because the ‘SQL server objects task’ that performs this operation uses SMO under the hood; And as of now, SMO is partially supported by SQL Azure.