Import SQL Azure database to a local SQL Server using SSIS

I had to download a SQL Azure database to my local SQL Server database. I searched the internet and found this stackoverflow article. I decided to try the SSIS option, I just wanted to share my experience of downloading the SQL Azure db using SSIS.

I created a new blank database in my local SQL Server. Once I created the database, I right clicked and navigated to the Tasks->Import Data… option, it shows up the first page of the SSIS wizard as in the image below.

SSIS Import Wizard Start Page

We just need to click on the next button here to get to the screen, where we setup the connection properties to enable the import of the SQL Azure database.

Selecting the database provider to connect to SQL Azure

In this step, the first configuration we need to make is selecting the appropriate provider, we have to select the “.Net Framework Data Provider for SqlServer” as you can see in the image above.

Selecting the appropriate Transport Protocol to connect to SQL Azure using SSIS

The second configuration is to select the appropriate transport protocol to connect to the hosted SQL Azure, we are selecting “TCP/IP (DBMSGNET)”

Providing the connection string properties like username, password, database name and servername

The third configuration is the connection string properties like user name, password, database name and the server name, you can see the highlighted fields in the image above.

Selecting the option to copy one or more database objects

After we setup the provider, protocol and connection properties, we click on the next button at the bottom of the wizard page, which leads to the step above. In this step, we are selecting the option to “Copy data from one or more tables or views”, clicking on the next button gives the option to select the required database objects like tables, views or stored procedures.

Selecting the required database objects

In the next step shown above, you can see I have selected all the database tables available, but if you want to select specific objects you are allowed to do so. If you want additional options you can select one of the tables in the grid and click on the “Edit Mappings…” to select specific options that would apply to your case. I am not going to go into all the details of SSIS, since SSIS by itself has quite a few things you can do with it.

Pre final SSIS step to select either to run immediately or save the SSIS package

This is the pre-final step, here you can either opt to run it immediately or save the package for later use, if you are planning to do this operation several times in the future, it is a good idea to save it.

It is a breeze so far, but there will always be something that will not work straight away. In my case, I had a problem with importing database tables that had the Varbinary(max) datatype column in it. So after some research, I found this great article by Wayne Walter Berry that provides the solution for that particular issue. Please note I am also using the SQL Server R2 edition of the database on my local.

Please provide feedback, suggestions or share your thoughts on any other and better ways of achieving the SQL Azure database import/download.

I do not get paid for blogging, Please like/share this article if you like it.

• «Previous Post
• Next Post »