Share via

How to use SSIS to migrate data to Azure database from on-premise database

  • Article

Today I read an article from msdn, it described Azure database support ODBC, so I did a test to migrate data from on-premise to Azure database.

General Guidelines and Limitations (Windows Azure SQL Database)

https://msdn.microsoft.com/en-us/library/windowsazure/ee336245.aspx#dlaps

Let’s start to see how to use SSIS to migrate data from on-premise database to Azure.

  1. Create a DSN via ODBC. The most step is similar with traditional ODBC configuration, just pay more attention on the server name format, SQL authentication, and default database.
  2. Now I had a DSN named “AzureDB”, the default database is destination Azure database
  3. Using SQL Server Data Tools to create an Integration Service Project.
  4. Drag a “Data Flow Task” in Control Flow
  5. In Connection Managers, create a new “OLE DB Connection Manager” and choose the on-premise database; Then create a new “ODBC Connection Manager” and choose the Azure database
  6. In Data Flow, drag “OLE DB Source” and “ODBC Destination”, then configure the “OLE DB Connection Manager” to the OLE DB Source and choose the table you want to migrate, meanwhile configure “ODBC Connection Manager” to ODBC Destination and choose the table you created in Azure database.
  7. Then run the SSIS package, we can see the data migrate to Azure database now.

Enjoy it!