In this article, we will explore how to split data within an Excel file and upload it to tables in an Azure SQL database. This process can be achieved using SQL Server Integration Services (SSIS) and ADO.NET as the destination.

First, let’s create the necessary tables in the Azure SQL database. We will create two tables: “AmericanCountries” and “AsianCountries”. Here is the script to create these tables:

CREATE TABLE ASIANCOUNTRIES (
    COUNTRYID INT IDENTITY (1, 1) PRIMARY KEY CLUSTERED,
    COUNTRYNAME VARCHAR(300),
    COUNTRYTYPE VARCHAR(200),
    LATESTRECORDEDPOPULATION INT,
    CONTINENT VARCHAR(100),
    REGION VARCHAR(50),
    SUBREGION VARCHAR(100)
);

CREATE TABLE AMERICANCOUNTRIES (
    COUNTRYID INT IDENTITY (1, 1) PRIMARY KEY CLUSTERED,
    COUNTRYNAME VARCHAR(300),
    COUNTRYTYPE VARCHAR(200),
    LATESTRECORDEDPOPULATION INT,
    CONTINENT VARCHAR(100),
    REGION VARCHAR(50),
    SUBREGION VARCHAR(100)
);

Next, we need to prepare the Excel file that contains the data. Let’s assume we have an Excel file named “Application_Countries.xls” with the country details.

To configure the Excel source in SSIS, follow these steps:

1. Open SQL Server Data Tools and create a new SSIS project named “ExportData.sln”.
2. In the SSIS package, drag and drop a “Data Flow Task” onto the Control Flow window and rename it to “Export Countries to Azure SQL”.
3. Double-click on “Export Countries to Azure SQL” to open the Data Flow window.
4. Drag the “Excel Source” component onto the Data Flow window and rename it to “Countries”.
5. Double-click on “Countries” to open the Excel Source Editor.
6. Click on “New” to configure the Excel Connection Manager.
7. Browse and select the Excel file “Application_Countries.xls”.
8. Select “Table or view” as the Data access mode and choose “Sheet1$” as the name of the Excel sheet.

Now, we need to split the data based on certain conditions and direct it to the appropriate tables. To achieve this, we will use the “Conditional Split” transformation in SSIS.

Configure the “Conditional Split” transformation as follows:

• If the value of the continent column is equal to “Asia”, the data will be stored in the “AsianCountries” table.
• If the value of the continent column is equal to “North America”, the data will be stored in the “AmericanCountries” table.

Once the conditional split is configured, we can configure the ADO.NET destination to upload the data to the tables in the Azure SQL database.

To configure the ADO.NET destination, follow these steps:

1. Drag and drop two ADO.NET destination components onto the Data Flow window.
2. Rename the first component to “AsianCountries” and the second component to “AmericanCountries”.
3. Double-click on each component to configure the ADO.NET connection.
4. Provide the Azure SQL Server name and login credentials.
5. Select the appropriate table or view for each destination component.

Finally, connect the data path from the conditional split to the corresponding ADO.NET destination based on the condition. This will direct the data to the correct table.

Once everything is configured, execute the SSIS package. If the package executes successfully, you can verify the data by querying the Azure SQL database using the following queries:

SELECT * FROM AMERICANCOUNTRIES;
SELECT * FROM AsianCountries;

That’s it! You have successfully split the data within the Excel file and uploaded it to the tables in the Azure SQL database.

By using SSIS and ADO.NET, you can automate the process of importing data from Excel to SQL Server, making it more efficient and reliable.