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 Join types overview and tutorial SQL Join类型概述和教程
- A step-by-step walkthrough of SQL Inner Join SQL内部联接的分步演练
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.
创建项目后,您可以返回目录并在文件夹中查看与项目相关的文件。