Nisarg Upadhyay
Entire package

How to export data from Excel into multiple tables in Azure SQL Database using SSIS

March 3, 2020 by

In this article, I am going to explain how we can split the data within the excel file and upload it to the tables created on the Azure SQL database.

To demonstrate the process, I have created two tables in the “AzureDatabase” database, which is hosted on the Azure SQL Server instance. The names of the tables are “AmericanCountries” and “AsianCountries.” Following is the Create Table script.

I have created an excel file named “Application_Countries“, (see attached below), that has details of countries. To export data to multiple tables, we are going to do the following:

  1. Export the data of the American Countries to the “AmericanCountries” table
  2. Export the data of the Asian countries to the “AsianCountries” table

To filter the data, we are going to use

  1. The conditional split component of the SQL Server integration services
  2. Excel file as a Source
  3. ADO.NET as a destination

First, let’s configure the Excel Source.

Configure Excel Source

First, Open the SQL Server data tools Create a new SSIS project named “ExportData.sln.” In the SSIS package, Drag and drop the “Data Flow Task” in the Control Flow window and rename it to “Export Counties to Azure SQL.” See the following image:

Add data flow task

Double-click on “Export Countries to Azure SQL.” On the “Data Flow” window (Screen 1), Drag Excel Source and rename it to Countries. Double click on “Countries.” On the “Excel Source Editor” dialog box (Screen 2), Click on New. On Excel Connection Manager (Screen 3), click on Browse to locate the file and click ok to close the Excel Connection Manager dialog box and click OK to close the Excel Source editor dialog box. See the following image:

Configure excel source

Select “Table or view” from the Data access mode drop-down box and select Sheet1$ in the “Name of the excel sheet” drop-down box. See the following image:

Configure excel data access mode

Now, to export data in different tables based on the condition, we must use conditional Split transformation.

Configure the conditional split component

To configure conditional split transformation, drag and drop the Conditional Split transformation from SSIS data tools to the Data Flow Task window. See the following image:

Drag and drop conditional split

As I mentioned, we want to copy the data of the “Asia” continent to the “AsianCountries” table and data of the “North America” continent to the “AmericanCountries” table. To do that, we will configure the conditional split transformation as follows:

  1. If the value of the continent column is equal to the “Asia,” then the data will be stored in the AsianCountries table
    1. Case 1: [Continent]==”Asia”
  2. If the value of the continent column is equal to the “America,” then the data will be stored in the AmericanCountries table
    1. Case 2: [Continent] == “North America”

To configure it, double-click on “Divide the data based on the condition“. Conditional Split transformation editor opens. In the editor, configure the condition cases as explained above. See the following image. Click OK to close the editor.

Configure conditions in conditional split

The Conditional split transformation is configured, let us configure the ADO.NET destination now.

Configure ADO.NET destination

To configure the ADO.NET, drag and drop the two ADO.Net destination components in the data flow window. Rename the first component to “AsianCountries” and second component to “AmericanCountries.” See the following image.

Drag and drop ADO.Net connections

Now the process of configuring ADO.Net destination is the same; hence I will explain the process to configure the ADO.Net component AsianCountries.

To do that, double click on it.

provide input to the ADO.Net destination

As you can see, when you double click on it, you will receive an error that says we did not provide any input columns to the ADO.Net component. We can ignore the error and click on “Yes.” A dialog box “ADO.Net destination editor” opens (Screen 1). On the “ADO.NET Destination Editor” dialog box, click on New. The dialog box, “Configure ADO.Net Connection Manager,” opens (Screen 2). Click on New on it. See the following image:

Configure ADO.Net destination

Another dialog box, “Connection Manager” opens. In the Dialog box, Provide the Azure SQL Server name in the “Server Name” text box. You can find the server name on the Azure Portal. Login to Azure Portal Open Azure SQL Database resource pool page.

Configure firewall rules to connect SQL Azure Database

In the authentication drop-down box, select “SQL Server Authentication.” Enter the user name and password, which is used to connect the Azure SQL Database. Select the database name from the “select or enter database name” drop-down box. See the following image.

Configure ADO.Net destination to connect Azure SQL database

Once the connection is configured, Click OK on Test connection. You will see the following error message.

Error while testing the connection

Error message:

Test connection failed because of an error in initializing provider. Cannot open server ‘companyemployees’ requested by the login. Client with IP address ‘43.228.96.58’ is not allowed to access the server. To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect.

The error message clearly says that we must add the IP of the server in the Azure firewall rule to allow the incoming connections. You can configure it from the “Firewall settings” page of the Azure portal. To add the client IP, open the Firewall settings page and click on “Add client IP” It automatically adds the IP address. You can also provide the range of the IP address in “Start IP” and “End IP” text box. See the following image:

Add firewall rule to access Azure SQL Database

Once the firewall has been configured, click on the “Test Connection” button. The connection will be established successfully.

Re-Test the connection

On the “ADO.Net destination editor” dialog box, select “AsianCountries” from the table or view name drop-down box. See the following image:

Select destination table 1

Similarly, configure the “AmericanCountries” ADO.Net destination editor. The only difference is that you must select “dbo.AmericanCountries” from Use a table or view drop-down box. See the following image.

Select destination table 2

Now to configure the data path, drag the blue data path arrow from the Divide the data based on the condition to the AsianCountries ADO.Net destination. When we connect the data path to the ADO.Net destination, a dialog box will appear, which will allow us to choose the output. Using it, we can determine the destination where we want to direct the output. The first output of the conditional split will be directed to the “AsianCountries“, hence choose “Asia” from the “Input Output Selection” dialog box. See the following image:

Configure Input for Asian countries

The second output will be exported to the AmericanCountries table hence select “America” from the Output drop-down box. See the following image:

Configure Input for American countries

The entire Data Flow Task looks like the following image:

Entire package

Now click on Execute to run the package. If package executes successfully, it should look like the following image:

Package executed successfully

You can verify the data by querying the Azure SQL database. Execute the following queries to review the output:

The following screenshot shows the output of the AsianCountries table:

Output of AsianCountries table

And below is the output of the AmericanCountries table.

Output of AmericanCountries table

Summary

In this article, I have explained how can we use the SSIS package to split the data within the excel file and upload it to the tables created in the Azure SQL database.

Application_Countries.xls

Nisarg Upadhyay
Latest posts by Nisarg Upadhyay (see all)
2,009 Views