How to import data in SQL Azure from SQL 2008 R2 using SSIS
When working with SQL databases one of the most common tasks is to import or export data from a database. In experimenting with SQL Azure, I realized that it is not possible to migrate data using the familiar Import and Export wizard that is available in SQL Server Management Studio (SSMS). What you have to do it is create a SSIS (SQL Server Integration services) package in Business Intelligence Development Studio (BIDS) and then execute it.
In this article we are going to show you how to copy data from on-site SQL 2008 to off-site SQL Azure. For this example we are going to export data from Products table in a local database to a database in the cloud using SQL Integration Services.
Before we jump into SSIS, we found another thing that is unique to SQL Azure version. We noticed that we need to have a clustered index on the destination table before SSIS will let us import data into it. So we have to do two things on the destination SQL Azure database first
-Create the Products table
-Define a clustered index on the table
We have included the SQL script for the above two steps for your convenience.
--This script creates the Products table on SQL Azure.
--Next we create a clustered index on ProductID
CREATE TABLE [dbo].[Products](
[ProductID] [int] NOT NULL,
[ProductName] [nvarchar](40) NOT NULL,
[SupplierID] [int] NULL,
[CategoryID] [int] NULL,
[QuantityPerUnit] [nvarchar](20) NULL,
[UnitPrice] [money] NULL,
[UnitsInStock] [smallint] NULL,
[UnitsOnOrder] [smallint] NULL,
[ReorderLevel] [smallint] NULL,
[Discontinued] [bit] NOT NULL
)
---ON [PRIMARY]
CREATE CLUSTERED INDEX IX_Product ON dbo.Products
(
ProductID
)
GO
Before getting started please realize that we are working with Enterprise edition of SQL Server 2008R2 and cloud version on SQL Server.
First off launch the Business Intelligence Development Studio or BIDS from the start menu by doing the following:
Start – All Programs – Microsoft Visual Studio 2008 - Microsoft Visual Studio 2008
This will launch the Business Intelligence Development Studio. Next from the File menu and do the following:
File - New - Project
Next go ahead and select Integration Services Project. This is shown in the dialog box below:
 |
| Integration Services Project |
You can add a name for the project and then click OK. This action will go ahead and create the necessary components under Solution Explorer. In addition it will also launch the Package Design window in the middle. The next thing you need to do is to add a data source that will be used to export data. Within the Solution Explorer, right click on Data Sources and then select New Data Source. This will open up the Data Source wizard screen, go ahead and click on Next. If you do not have an existing data connection, you can create one by clicking New.
In this example we are going to use sample Northwind database on a local instance of SQL server 2008 R2 to export data. This SSIS data source is for server KASHMONEY-PC\SQL08R2 as shown in the following computer screenshot.
 |
| On-site SQL 2008 R2 |
The next thing we need to do is to define the data destination where data will be imported into. In this case this we will use a SQL Azure database in the cloud. In order to create this connection, we need to define another data source so we are going to follow the earlier step.
We will invoke the Data Source Wizard one more time as shown below.
Here we had some issues as we tried different data providers in order to connect successfully to a SQL Azure server and the database. Bottom line is you need to use the .Net Providers\SqlClient data provider as shown below in the Connection Manager screen.
In addition you will need to know the server address, SQL username and the SQL password.
 |
| SQL Azure Connection |
Next click OK which will finish the wizard. At this point, we have our data source and are data destination all squared away. We have included a screen capture that shows the end of the data wizard with the connection string information.
 |
| Destination Database |
Click Finish to exit out of this.
Now we are going to configure the SSIS package details, remember an Integration package is the building block for data movement in SQL Server. Make sure that you are on the Control Flow tab in design pane. From the Toolbox on the left, drag the Data Flow task under the Control Flow Items onto the design surface.
This step has been displayed for you in the following figure.
 |
| SSIS Package |
Next switch to the Data Flow tab on the SSIS design pane. Next we will be adding a data source from our local on premise database. We drag the OLE DB Source from the Data Flow Sources.
Here is what it looks like on our puter monitor.
 |
| OLE DB Source |
We need to configure the SSIS data flow item. Right click on OLE DB Source and select Edit. On the next dialog OLE DB Source Editor, go ahead and select New. This will open up a new screen as shown below.
 |
| On Premise SQL Server |
From the data connections pane, select LocalHost.Northwind and select Ok.
So far we have just made the connection to the source database, we still need to get the table information. Here you will select Table or view under Data access mode. Next choose Products under Name of the table or the view. After you click Ok, you have successfully created SSIS data flow from the source. This step is shown below:
The next step is to create the ADO Net Destination, this Is where the data will be imported into. From the Toolbox on the left, go ahead and select the Ado Net destination SSIS Data Flow item and then move it over to the design pane in Data Flow tab. This is shown in the screen capture right below, notice that the connection has not been configured yet and is displayed in red.
 |
| Data Flow controls |
Before you can configure the ADO NET destination, you have to drag the output (green arrow) from the OLE DB Source to the ADO NET Destination connection. Next you can right click on ADO NET destination and select Edit. This will launch a dialog box titled ADO NET Destination Editor.
Go ahead and click on New. You will get the Connection Manager screen shown below.
Go ahead and select the SQL Azure connection that was defined in earlier step. Also you will need to point to the table we created using SQL Script. For Use a table or view select Products. Here is what is looks like on our Microsoft Visual Studio 2008 application.
One final thing we need to do before we can import data using Integration Services is to do map the columns between the data source and the data destination. If you are using the same table schema structure on the source and the destination tables, this should be pretty straight forward.
Click on Mappings in the left pane and make sure the Input and Output columns match. We have included a screen shot for your review.
 |
| Column Mappings |
Let us take a look at our Data Flow designer before we kick things off. We have done this so far
1- Defined a local on premise server connection to Northwind database
2- Defined a cloud SQL Azure server connection to an off site database
3- Defined a OLE DB source that maps to local on-site database connection
4- Defined a ADO NET destination that maps to an off-site cloud SQL Azure database connection
Here is what we have so far, we are almost done.
 |
| Integration Services Package |
Now we are going to kick off the SSIS package, all you need to do is click on Execute (green arrow) or Debug – Start Debugging. In our case, all the items are configured properly and our SSIS package executed with success. we have the following end result and were able to move 77 records from SQL 2008 to SQL Azure.
 |
| Execute SSIS package |
We can verify the data on the target SQL Azure location with this SQL query.
 |
| Check data at Target |
We were successful in migrating data from an on premise local database to a SQL Azure cloud database using SQL Server Integration Services.
More tips on SQL Azure can be found at our site:
http://sqlazuretutorials.com/
No comments:
Post a Comment