Merged Components Are Not Valid

SSIS Merge Bring together and Merge transformations are 2 like components used to combine ii input data sources into 1 output. This article explains both transformations and illustrates when and how to use each one of them.

This article is the fourteenth of the SSIS features face-to-face series that aims to compare similar SSIS components in order to remove any confusion for the information integration developers that use this engineering science.

• Annotation: All examples in this article are made using the Stack overflow 2013 database, SQL Server 2019, Visual Studio 2019, SQL Server Integration Services projects extension version iii.iv.

SSIS Merge Transformation

As mentioned in the SSIS toolbox, SSIS Merge transformation "combines rows from multiple sorted data flows into one sorted information flow".

Figure 1 – Merge Transformation description in the SSIS toolbox

Merge transformation is very similar to the Marriage All Transformation, which combines rows from different sources into one output. The primary difference is that Union All doesn't crave that the data sources are sorted, nor does its output. Besides, Union All accepts more than two inputs while Merge transformation doesn't.

Before illustrating how to use the Merge transformation, we will explain several approaches to sort information sources in SSIS.

Using Sort Component

Sort Component is a transformation that sorts input information in ascending or descending order "when it cannot be sorted at its source". This means that – in general – sorting data at its source is preferable to using this component. The reason is that sorting data at the source level decreases the workload on the ETL server. Besides, it can benefit from indexes or other helpers institute at the data source.

Effigy two – Sort Component clarification in the SSIS toolbox

Ane important notation mentioned in the SSIS toolbox is that the Sort component is not recommended for large information since information technology requires loading all information in retention before generating the sorted output.

The Sort component is very simple and easy to apply; you lot need to specify the columns used in the sort functioning with the sorting order for each i of them.

Figure iii – Sort Component editor

Caution: Removing duplicates can result in a data loss since duplicates are compared based on the sorting columns, non the whole data row.

Figure iv – Using the Sort component with the Merge transformation

Using Avant-garde Editor

As we mentioned before, when it can sort the data at its source, there is no need to use the Sort component. Assuming that nosotros are reading from a SQL Server tabular array having a clustered index (which means that its primary key columns sort data), or we used an ORDER BY clause in the SQL control. SSIS engine does not automatically detect our data source is sorted, and the user must tell it so.

To handle a data source equally it is sorted, we should open the source component'due south advanced editor (Right-click > Show Avant-garde Editor…).

Figure five – Opening source's avant-garde editor

In the source component advanced editor, go to the "Input and Output Backdrop" tab. Click on the "OLE DB Source Output" and change the "IsSorted" property to "True".

Effigy 6 – Changing the "IsSorted" property to "True"

The next step is to configure the sorting key position for all columns used in the sort operation.

Effigy 7 – Configuring the sort key position

As mentioned in the official documentation, the value of the "Sort Key Position" property should exist configured as follows:

• The integer value must correspond a numeric sequence, starting with 1 and incremented by ane
• A positive integer value indicates an ascending sort club
• A negative integer value indicates a descending sort guild. (If fix to a negative number, the absolute value of the number determines the column'south position in the sort sequence)
• The default value of 0 indicates that the column is not sorted. Get out the value of 0 for output columns that exercise non participate in the sort

Using the SSIS Merge transformation

To illustrate the SSIS Merge transformation, let us presume that we demand to insert all data from the "posts" and "comments" tabular array into one table. To do so, we created an SSIS package, added a "Data Menses Task", and configured an OLE DB connexion to the Stack Overflow 2013 database. Next, we added 2 OLE DB Source components and a Sort component for each one. And then, we add together a Merge transformation that takes both Sort components' output as input.

Every bit shown in the image below, the merge transformation editor is very simple. You simply need to specify the component output columns and their respective column from each input.

Figure eight – Merge transformation editor

As shown in the image beneath, the "ID" output cavalcade is the sort key for the component output, and it is mapped to the "ID" columns of each data source (the sorting keys).

Figure nine – Information flow task screenshot

If we open the OLE DB destination advanced editor, we can check that the Id cavalcade sorts the Merge transformation output.

Effigy ten – OLE DB Destination Input backdrop

Figure eleven – SortKeyPosition property for the ID column

After executing the package, we tin can annotation that the count of Merge transformation output rows is equal to the sum of both input rows.

Figure 12 – Merge transformation Inputs and output rows count

SSIS Merge Join Transformation

The SSIS Merge Join Transformation combines 2 sorted inputs data flows into one output using a join operation. Three join operations are supported: Total, Left, and inner join.

Effigy xiii – SSIS Merge Join transformation description in the SSIS toolbox

The epitome below shows that the SSIS Merge Bring together transformation joins both inputs using the sorting key used in each data source. You can select the output columns from both sources.

Figure 14 – SSIS Merge Join transformation editor

Since correct join is not listed in the "Bring together Type" driblet-downwardly list, y'all tin can choose the left join option and click on the "Swap Inputs" button to bandy the input from the right to the left side, which is equivalent to the RIGHT OUTER JOIN functioning.

The SSIS Merge Join transformation output rows differ based on the join blazon selected:

• INNER JOIN: This type of join returns those rows which have matching values in both information sources
• LEFT OUTER Bring together: This type of join returns all rows from the left-side data source with all matching rows from the right-side source
• Full JOIN: This type of join returns all matching rows from left and right-side information sources

SSIS Merge Bring together vs. Merge transformation

The difference between the SSIS Merge Join and the Merge transformation is like the difference betwixt the UNION and JOIN operators in SQL Server. Both are used to combine rows from two information sources, but each has its ain mode of merging them. While Merge transformation is used to combine rows (such as UNION operation), SSIS Merge Join transformation is used to combine columns between unlike rows (such as SQL Joins).

Summary

In this article, we explained both SSIS Merge Join and Merge transformations. We besides illustrated how to configure them. As well, nosotros explained how to sort data sources since information technology is a requirement for merging transformations in SSIS. Finally, we explained the principal difference between both merging components in SSIS.

Tabular array of contents

SSIS OLE DB Source: SQL Command vs Table or View

SSIS Expression Tasks vs Evaluating variables as expressions

SSIS OLE DB Destination vs SQL Server Destination

Execute SQL Task in SSIS: SqlStatementSource Expressions vs Variable Source Types

Execute SQL Task in SSIS: Output Parameters vs Result Sets

SSIS Derived Columns with Multiple Expressions vs Multiple Transformations

SSIS Data types: Change from the Avant-garde Editor vs Data Conversion Transformations

SSIS Connectedness Managers: OLE DB vs ODBC vs ADO.NET

SSIS Flat Files vs Raw Files

SSIS Foreach Loop vs For Loop Container

SSIS: Execute T-SQL Statement Task vs Execute SQL Chore

SSIS Lookup transformation vs. Fuzzy Lookup transformation

SSIS Pivot transformation vs. Unpivot transformation

SSIS Merge Join vs. Merge Transformation

Information Admission Modes in SSIS OLE DB Destination: SQL Command vs. Table or View

SSIS XML Source vs XML task

SSIS Script task vs. Script Component

SSIS term extraction vs. term lookup

• Writer
• Contempo Posts

Hadi is an SQL Server professional with more than 10 years of experience. His main expertise is in data integration. He'south 1 of the top ETL and SQL Server Integration Services contributors at Stackoverflow.com . Also, he published several article series about Biml, SSIS features, Search engines, Hadoop, and many other technologies.

Besides working with SQL Server, he worked with dissimilar data technologies such as NoSQL databases, Hadoop, Apache Spark. He is a MongoDB, Neo4j, and ArangoDB certified professional.

On the academic level, Hadi holds two master's degrees in computer science and business computing. Currently, he is a Ph.D. candidate in data science focusing on Big Data quality cess techniques.

Hadi really enjoys learning new things everyday and sharing his cognition. You tin can attain him on his personal website.

View all posts by Hadi Fadlallah

hendersonmingly47.blogspot.com

Source: https://www.sqlshack.com/ssis-merge-join-vs-merge-transformation/

Share this post