How to use Import and Export wizard (DTS) to transfer data to or from a SQL Azure database?

A lot of people are starting with experiments to look to the possibilities of SQL Azure. One of the things to start is a database with content. As described in one of my previous blogs, you can use the SQL Azure migration wizard. This is a nice tool, but most people are already aware of the Import and Export Wizard in SQL Server Management Studio (SSMS), also known in the past as Data Transformation Services DTS.

In this blog I will describe how you can use the Import and Export Wizard to transfer data from an on-premise SQL Server database to an SQL Azure database. What do you need to have to start using the Import and Export wizard.

1. Windows Azure subscription
2. Create a SQL Azure server and database
3. Configure the SQL Azure firewall
4. On premise SQL Server with SQL 2008 R2 with SSMS.
5. Create the tables you need in the SQL Azure database.
6. Create a clustered index on each table to which you want transfer data. Without a clustered index you CAN'T transfer data to a SQL Azure database.

Now you have done all preperations we can start the Import and Export Wizard. Click Start, All Programs, SQL Server 2008 R2, Import and Export Data. The Import and Export wizard is now started.

1. Choose a datasource. This is your on-premise SQL Server.
2. Choose a destination. This will be your SQL Azure database.
1. Destination: .NET Framework Data Provider for SqlServer
2. Encrypt: True
3. Integrated Security: False
4. Password: SQL Azure administrator password
5. TrustedServer: Certificate: True
6. User ID: SQL Azure administrator name. [username]@[sqlAzureservername]
7. Data Source: FQDN of your SQL Azure Server
8. Inintial Catalog: The SQL Azure database you have created to insert data.

3. Press next and execute the same steps as you normally do in the Import and Export wizard.
4. In case you forgot to create a clustered index you will get next message:
1. Messages
   
   Error 0xc020844b: Data Flow Task 1: An exception has occurred during data insertion, the message returned from the provider is: Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again. (SQL Server Import and Export Wizard.

Enjoy using the Import and Export wizard to transfer data to your SQL Azure database.
Tweet