How to import an Excel sheet into multiple SQL tables using SSIS

Today we will be looking on how to import an excel sheet into multiple tables. Let’s take as an example the following excel spreadsheet:

Looking at the excel above we can identify two problems:

1) We need to split a sheet into three tables

2) No identifier columns

For this task we will be using SQL Server Integration Services. So let’s run the SQL Server Business Intelligence Development Studio and create a new Integration Services Project:

Now let’s create our data flow:

First of all we need to establish a connection to our Excel file. On the bottom , right-click on the Connection Managers and create a New Connection:

Next we will choose the Excel connection and point to our source file:

Now that we have our connection we need to add a Excel Source to our Data Fow:

Next double-click on the Excel source to change its settings like so:

If all is well we should be able to preview the data:

Now, one of the problems we identified earlier was the lack of a identifier column for both stores and products. So first we need to solve this with the help of the Derived Column Data Flow Transformation:

First we connect the Excel Source with the Derived Column. Then we double-click the Derived Column and configure it like so:

Ok, we now have the identifier columns but still the is no value for them. Let’s fill that gap. We need to ensure that every time the Excel is imported the same key is generated. We can do that with the help of MD5. So let’s add a Script Component to our Data Flow, setup and add the c# code for this task:

1) First we need to connect the Derived Column to our Script Component;

2) Secondly when we double-click the Script Component we must choose the Transformation Mode;

3) Next we go to the Input Columns and choose the new Derived Columns and also the STORE and COD Columns;

4) Finally we add our code to the component:

And the code:

public override void Input0_ProcessInputRow(Input0Buffer Row)

{

Row.ProductId = Encode(Row.COD.ToLower());

Row.StoreId = Encode(Row.STORE.ToLower());

}

private Guid Encode(string text)

{

System.Security.Cryptography.MD5CryptoServiceProvider x = new System.Security.Cryptography.MD5CryptoServiceProvider();

byte[] bs = System.Text.Encoding.UTF8.GetBytes(text);

bs = x.ComputeHash(bs);

System.Text.StringBuilder s = new System.Text.StringBuilder();

foreach (byte b in bs)

{

s.Append(b.ToString("x2").ToLower());

}

text = s.ToString();

return new Guid(text);

}

Like I said before, we will be using MD5 crypto to generate our unique columns. Sweet. One problem solved.

Now that we got our final source table we need to execute multiple operations on it. For that we will be using the MultiCast Transformation:

So, first lets populate the Products Table. We need a way to get the list of distinct products. For that we will use the Sort Transformation:

We first select the columns that we want to use and most importantly we must select “Remove rows with duplicate sort values” to have our distinct filter.

Let’s try it to see if it works. For that we will be adding a Grid Data Viewer to the connection between the Script Component and the Multicast. We, for test purpose only, will also add a Data Reader Destination and connect the Sort Transformation to the Data Reader Destination. Then will add a new Data Viewer to the connection.

So to add a Data Viewer simple right-click on the connection between components, click Add, select Grid type and click OK twice: