SQL Server Integration Services Data Sort and Merge

Introduction

In this article, I will be covering the Sort and Merge components of SSIS. As you can tell, these components will sort and merge different sets of data. The reason that both components are being covered in the same article is that SSIS cannot merge data sets unless both data sets are sorted by a particular column. The sorted column will most likely have the same name and data type between the two data sets. As you learn more about SSIS, you will find that the Sort component will be one of most common components used.

If you're looking for a really good web host, try Server Intellect - we found the setup procedure and control panel, very easy to adapt to and their IT team is awesome!

Implementation

For this article, I will be building on the previous article SSIS: Import. Please refer to that article for setting up your Visual Studio environment. To set up the scenario, the basic technique to import data from the last article will be used. This time, a second flat file will be added. For each flat file, the data will be sorted by a common column name and then merged before it is inserted into the database. To jump right in, I created a new package in the same project. To do this, right click the SSIS Packages folder in Solution Explorer and then click New SSIS Package. Use the knowledge learned from the last article to configure the Data Flow and then inside the Data Flow, configure a Flat File Source and an OLE DB Destination along with their appropriate connections. Change the name of the Flat File Source to Vendor A by right clicking and selecting Rename.

NOTE: If you are using the database table from the last article, be sure to run the included change script for the table or just remove the primary key and identity parameters for the TransactionID column.

Before the second Flat File Source can be configured, we need to create a new connection manager that will access the data in the second flat file.

We used over 10 web hosting companies before we found Server Intellect. Their dedicated servers and add-ons were setup swiftly, in less than 24 hours. We were able to confirm our order over the phone. They respond to our inquiries within an hour. Server Intellect's customer support and assistance are the best we've ever experienced.

Now a second Flat File Source component needs to be configured to access the additional flat file found in the import folder using the same configuration format as the other flat file source.

Rename the Flat File Source to Vendor B. Now find the Sort component in the toolbox and drag one under the Vendor A flat file source then connect the output of the flat file source to the Sort component.

Next the Sort component needs to be configured so double click the component to bring up the editor. All columns automatically are checked to be passed through but if a column is not needed then it is here you can uncheck the pass through box. The main task to be done here is pick a column that needs to be sorted on. For this example, the TransactionID column is the one that needs to be sorted. Note that you can change the name of the output column to suit your needs.

If you're ever in the market for some great Windows web hosting, try Server Intellect. We have been very pleased with their services and most importantly, technical support.

Click OK. Repeat the dragging a Sort component into the work area and configure it for the Vendor B Flat File Source. Make sure and sort on the TransactionID column to keep things simple.

Since the Sort components are both configured it is time to merge them into one data set. Find the Merge component in the toolbox and drag into the work area. Select the Vendor A Sort and connect the output to the Merge component. When this happens a dialog box appears to ask which input of the Merge component to attach it to. Select Input 1 as shown.

Do the same for the output of the Vendor B Sort component. Since there can only be two inputs for a Merge component, Visual Studio automatically assigns it to Input 2. To make sure the data lines up properly, double click the Merge component. For each input the column names are listed side by side. Each input should match the other if the same columns are sorted correctly.

We are using Server Intellect and have found that by far, they are the most friendly, responsive, and knowledgeable support team we've ever dealt with!

Once the data is merged properly, connect the output of the Merge component to the OLE DB Destination component. Configure the OLE DB Destination like the previous article but this time make sure and uncheck the Keep Identity check box since the TransactionID columns from the two files are not in sequential order.

Testing the Package

If there are no configuration warnings, it is time to test the package. Notice that when the package is done processing that you can see the number or rows being processed through each component. This gives some indication whether the package is processing correctly in addition to the components turning green.

What have we learned?

How to configure a Sort component.
How to configure a Merge component.
How to follow the number of rows are being processed through each component during testing.

Yes, it is possible to find a good web host. Sometimes it takes a while. After trying several, we went with Server Intellect and have been very happy. They are the most professional, customer service friendly and technically knowledgeable host we've found so far.

Attachments