ssis合并连接链接键_在SSIS包中使用合并联接转换

ssis合并连接链接键

This article explores the Merge Join Transformation in SSIS packages.

本文探讨了SSIS包中的合并联接转换。

介绍 (Introduction)

In a relational database, we use different normalizations to split data across multiple tables. Later, if we need to retrieve data together, we used multiple kinds of JOINS operator in SQL Server. These tables combine using a shared key or join key. For example, we can use the [EmpID] column to join tables related to employees.

在关系数据库中，我们使用不同的规范化将数据拆分到多个表中。后来，如果我们需要一起检索数据，则在SQL Server中使用了多种JOINS运算符。这些表使用共享**或联接**合并。例如，我们可以使用[EmpID]列来联接与员工相关的表。

I would recommend you to go through the following articles to get knowledge of JOINS in SQL Server.

我建议您阅读以下文章，以了解SQL Server中的JOINS知识。

SQL Server setup also provides SQL Server integration services (SSIS) to develop and implement SSIS packages that could do data transformation and data manipulations. On SQLShack, we have covered many useful transformations using SSIS packages. You can check these detailed articles Integration Services (SSIS) to learn more about them.

SQL Server安装程序还提供SQL Server集成服务（SSIS），以开发和实现可以执行数据转换和数据处理的SSIS程序包。在SQLShack上，我们介绍了使用SSIS包进行的许多有用的转换。您可以查看这些详细的文章Integration Services（SSIS），以了解有关它们的更多信息。

In this article, we will explore the Merge Join Transformation in SSIS package. It is useful to perform SQL Joins using the SSIS package. We can achieve Inner Join, Left Outer Join, Right Outer Join and Full Outer Join using this transformation.

在本文中，我们将探讨SSIS包中的合并联接转换。使用SSIS包执行SQL连接非常有用。使用此转换，我们可以实现内联接，左外联接，右外联接和全外联接。

样本数据–平面文件源 (Sample data – Flat file source)

For this article, I prepared two flat files (*.txt), as shown below. These files contain employee information, such as location and salary. We have an employee name field common in both files.

对于本文，我准备了两个平面文件（* .txt），如下所示。这些文件包含员工信息，例如位置和薪水。我们在两个文件中都有一个雇员姓名字段。

In terms of environment, I am using Visual Studio 2019 version 16.4.0. You can download the latest version of it using this link.

就环境而言，我正在使用Visual Studio 2019版本16.4.0。您可以使用此链接下载最新版本。

创建用于合并SSIS中的合并联接的ETL包 (Create an ETL package for Merge Join Transformation in SSIS )

Launch Visual Studio, create a new integration service project.

启动Visual Studio，创建一个新的集成服务项目。

On the next page, specify a project name and directory to save the package related files.

在下一页上，指定项目名称和目录以保存与软件包相关的文件。

Once it creates the project, you can go back to the directory and view the project-related files in the folder.

创建项目后，您可以返回目录并在文件夹中查看与项目相关的文件。

It opens the new project and solution, as shown below.

它将打开新项目和解决方案，如下所示。

Now, drag a data flow task from the SSIS toolbox and rename it to give an appropriate name. For rename, right-click on it and choose Rename.

现在，从SSIS工具箱中拖动一个数据流任务，并将其重命名为一个适当的名称。要进行重命名，请右键单击它，然后选择“重命名”。

Double-click on this task and add two flat file sources in the data flow area. We use the flat file source for connecting with text or CSV files.

双击此任务，然后在数据流区域中添加两个平面文件源。我们使用平面文件源来连接文本或CSV文件。

Rename both flat-file sources to reflect the flat file source.

重命名两个平面文件源以反映平面文件源。

Flat File Source -> EmpSalary
平面文件源-> EmpSalary
Flat File Source 1 -> EmpLocation
平面文件源1-> EmpLocation

We see a red cross on both flat files because we have not created connections to source files. I will show a connection for a flat-file here, and you can follow the similar steps for the second file as well.

我们在两个平面文件上都看到一个红叉，因为我们尚未创建与源文件的连接。我将在此处显示平面文件的连接，对于第二个文件，您也可以遵循类似的步骤。

Double-click on this flat file source, and it opens the following flat file source editor.

双击此平面文件源，它将打开以下平面文件源编辑器。

Click on New and fill out the following information’s in the General tab.

单击“新建”，然后在“常规”选项卡中填写以下信息。

Connection Manager name: SSIS package automatically takes a name for it, but it is recommended to give a proper name so that you can quickly identify connections in the package
连接管理器名称： SSIS程序包会自动为其取一个名称，但是建议提供一个适当的名称，以便您可以快速识别程序包中的连接
File Name: Specify the complete path of the source file. You can browse the file here or paste the complete file path
文件名：指定源文件的完整路径。您可以在此处浏览文件或粘贴完整的文件路径
In my example, the first row contains the table header. If you do not have a column header, uncheck the tick from Column names in the first data row
在我的示例中，第一行包含表头。如果您没有列标题，请取消选中第一个数据行中列名称的勾号

SSIS package automatically checks the format and text delimiter for the flat file. You can review and make changes if required.

SSIS程序包会自动检查平面文件的格式和文本定界符。您可以查看并根据需要进行更改。

In the bottom, you see a message that Columns are not defined for this connection manager. Navigate to Columns, verify the columns present in the flat file in preview.

在底部，您将看到一条消息，提示未为此连接管理器定义列。导航到Columns ，在预览中验证平面文件中存在的列。

Navigate to Advanced and verify the properties of the column. Here, you can make changes in column delimiter, data type, and output column width. We can go with the default values here.

导航到“ 高级”，然后验证列的属性。在这里，您可以更改列定界符，数据类型和输出列宽。我们可以在这里使用默认值。

Navigate to Preview and verify records appearing correctly.

导航到预览并验证记录是否正确显示。

Click OK, and we are done with the configuration for the first flat file source.

单击“确定”，我们完成了第一个平面文件源的配置。

Similarly, do the configuration for another flat file, as shown below.

同样，对另一个平面文件进行配置，如下所示。

Before we add a Merge Join Transformation in SSIS, we require a sort transformation with both flat file source. Sort transformation is similar to an ORDER BY clause that sorts the results in an ascending or descending order.

在SSIS中添加合并联接转换之前，我们需要对两个平面文件源进行排序转换。排序转换类似于ORDER BY子句，该子句以升序或降序对结果进行排序。

Drag two sort transformations and join them with the flat file source shown below.

拖动两个排序转换，并将它们与如下所示的平面文件源结合在一起。

We need to configure the sort transformation as well. Let’s open the sort connected with [EmpSalary] flat file source.

我们还需要配置排序转换。让我们打开与[EmpSalary]平面文件源关联的排序。

It shows the input columns from the flat file. Select all available columns, and you see below input columns, output column alias( by default same as of Input columns), sort type and sort order. The sort order defines which columns will be sorted first. It depends upon which column we selected first for the sorting.

它显示了平面文件中的输入列。选择所有可用的列，然后在下面看到输入列，输出列别名（默认情况下与输入列相同），排序类型和排序顺序。排序顺序定义将首先对哪些列进行排序。这取决于我们首先选择哪一列进行排序。

Click Ok, and the configuration is done for the first sort operator.

单击确定，并为第一个排序运算符完成配置。

Similarly, do the configuration for another sort operator, and you see it removed the red cross icon.

同样，为另一个排序运算符进行配置，您会看到它删除了红叉图标。

We have completed both sort transformation configurations.

我们已经完成了两种排序转换配置。

Now, its time to add Merge Join transformation in SSIS package.

现在，是时候在SSIS包中添加Merge Join转换了。

As you know, for a SQL JOIN we require at least two tables to join together. Similarly, here, we need to give input of data coming from both flat files after sort transformation.

如您所知，对于SQL JOIN，我们至少需要两个表才能连接在一起。同样，在这里，我们需要在排序转换后提供来自两个平面文件的数据输入。

Once you drag an output from sort operator to Merge Join, it opens the following pop-up for input-output selection. Here, we select the input either Merge Join Left Input or Merge Join Right Input.

将输出从排序运算符拖动到“合并联接”后，它将为输入输出选择打开以下弹出窗口。在这里，我们选择输入合并合并左输入或合并合并右输入 。

Let’s select the Merge Join Left Input and click Ok. Similarly, drag the output of the second sort to Merge Join transformation. It does not show an input-output selection window this time because we have already given input in the first sort transformation.

让我们选择“合并连接左输入”，然后单击“确定”。同样，将第二类的输出拖动到“合并联接”转换中。这次不显示输入-输出选择窗口，因为我们已经在第一个排序转换中提供了输入。

Now, we need to configure Merge Join as well. Double-click on it and open Merge Join Transformation Editor. It shows both input files data, and join type as Inner Join in this case.

现在，我们还需要配置合并联接。双击它，然后打开“合并联接转换编辑器”。在这种情况下，它既显示输入文件数据，又显示内部联接的联接类型。

Now, select the columns we require in the output. We have a common field [EmpName] in both data set. We can skip one column to avoid duplicate columns.

现在，在输出中选择我们需要的列。这两个数据集中都有一个公共字段[EmpName]。我们可以跳过一列以避免重复的列。

We also need to define the Join key accurately. Currently, in the previous screenshot, it has put a check on Join Key for all columns. Remove the check from other columns, and it should have Join key for [EmpName] column as shown below.

我们还需要准确定义Join键。当前，在上一个屏幕截图中，它已选中所有列的Join Key。从其他列中删除该支票，它应具有[EmpName]列的Join键，如下所示。

Click Ok, and we can see that configuration is complete for the Merge Join Transformation in SSIS.

单击“确定”，我们可以看到SSIS中的合并联接转换已完成配置。

For this demo purpose, we do not need to insert data in a SQL table. Let’s add an SSIS Multicast transformation and join it with Merge Join.

为此，我们不需要在SQL表中插入数据。让我们添加一个SSIS组播转换，并将其与Merge Join合并。

To view data, right-click on the connection line between both Merge Join and SSIS Multicast and click on Enable Data Viewer. You can see a data viewer symbol on the connection line.

要查看数据，请右键单击“合并联接”和“ SSIS多播”之间的连接线，然后单击“ 启用数据查看器”。 您可以在连接线上看到数据查看器符号。

Let’s execute the SSIS package now and view the results. Here, we see the expected result of the INNER JOIN of both flat files. We see in progress icon for Merge Join and Multicast because it is showing the data in a data viewer.

现在执行SSIS程序包并查看结果。在这里，我们看到两个平面文件的INNER JOIN的预期结果。我们看到“合并联接和多播”的进度图标，因为它在数据查看器中显示数据。

We can copy the data in the clipboard from here or close the data viewer screen. It shows the package completed successfully.

我们可以从此处复制剪贴板中的数据，或关闭数据查看器屏幕。它显示程序包已成功完成。

Now, stop the package execution and open the Merge Join transformation editor. Change the Join type from Inner Join to Left Outer Join.

现在，停止执行包并打开“合并联接”转换编辑器。将联接类型从“内部联接”更改为“左外部联接”。

Open the [EmpSalary] text file and remove the data for Andrew.

打开[EmpSalary]文本文件，然后删除Andrew的数据。

In the Left outer Join, we get output from the left-hand table along with the matching rows between both tables.

在左外部联接中，我们从左侧表中获得输出以及两个表之间的匹配行。

Once we execute the package now, it does not show a row for Andrew because it exits in the right-hand side table [EmpLocation]. Left outer join does not include unmatched rows from the right-hand side table.

现在，我们立即执行该程序包，因为它在右侧表[EmpLocation]中退出，所以它不会在Andrew处显示一行。左外部联接不包括右侧表中不匹配的行。

Let’s stop the package, and open Merge join transformation editor again. Click on Swap Inputs, and it changes the position of the tables. We can see [EmpLocation] table is on the left-hand side now.

让我们停止该程序包，然后再次打开“合并联接转换编辑器”。单击交换输入，它会更改表格的位置。我们可以看到[EmpLocation]表现在在左侧。

This [EmpLocation] table still contains data for Andrew. Rerun the package, and in the output, we get a row for Andrew as well because it is in the left-hand side table.

此[EmpLocation]表仍包含Andrew的数据。重新运行该程序包，在输出中，我们也为Andrew一行，因为它在左侧表中。

We get NULL values for the column because it does not have a corresponding row in the [EmpSalary] table.

我们获得该列的NULL值，因为在[EmpSalary]表中没有相应的行。

结论 (Conclusion)

In this article, we explored the usage of Merge Join Transformation in SSIS packages to perform SQL JOINS. It might be useful for beginners to learn the JOINS. You can also use it in ETL’s where you get data from multiple sources, and you need to perform the Join on them. It eliminates the need for importing data first in SQL tables, perform joins using SQL query and then do further data processing.

在本文中，我们探讨了SSIS包中合并联接转换的用法，以执行SQL JOINS。对于初学者来说学习JOINS可能会有用。您还可以在ETL中使用它，在ETL中，您可以从多个来源获取数据，并且需要对它们执行Join。它消除了先导入SQL表中的数据，使用SQL查询执行联接然后进行进一步的数据处理的需要。

翻译自: https://www.sqlshack.com/using-the-merge-join-transformation-in-ssis-packages/