ssis 有条件拆分_SSIS条件拆分转换概述

ssis 有条件拆分

This article explores the SSIS Conditional Split Transform task to split data into multiple destinations based on the specified conditions.

本文探讨了SSIS条件拆分转换任务，该任务可根据指定条件将数据拆分到多个目标。

介绍 (Introduction)

We apply different logics on data in SQL Server tables to fulfill the requirements of the end-users. We might apply different analytical, logical, arithmetic conditions and logic on data. Let’s say you have a requirement to split the data into multiple tables depending upon some conditions. You can use a T-SQL function to write, but it might take time to do the development work. You need to rework on the code in case there are logic changes.

我们对SQL Server表中的数据应用不同的逻辑，以满足最终用户的需求。 我们可能会对数据应用不同的分析，逻辑，算术条件和逻辑。 假设您需要根据某些条件将数据拆分为多个表。 您可以使用T-SQL函数进行编写，但是可能需要一些时间来完成开发工作。 如果发生逻辑更改，则需要对代码进行重新处理。

SSIS always comes handy in such situations and provides a solution for us with minimal complexity. Further, we can use it to take inputs from multiple data sources such as Excel, CSV, OLE DB, ODBC, TXT, etc. We can also use it to prepare output in different formats without writing complex programming codes.

在这种情况下，SSIS总是很方便，并为我们提供了最小的复杂性的解决方案。 此外，我们可以使用它来获取来自多个数据源的输入，例如Excel，CSV，OLE DB，ODBC，TXT等。我们还可以使用它来准备不同格式的输出，而无需编写复杂的编程代码。

环境细节 (Environment Details)

• We will use the AdventureWorks2017 sample database in this article
  我们将在本文中使用AdventureWorks2017示例数据库
• Download and install SQL Server Data Tools (SSDT) for Visual Studio to install it 下载并安装用于Visual StudioSQL Server数据工具（SSDT）进行安装

需求 (Requirement)

We have Employee data in the AdventureWorks2017 database. It contains a view vEmployee to fetch employee records.

我们在AdventureWorks2017数据库中有员工数据。 它包含一个视图vEmployee以获取员工记录。

Execute the following query to view the sample data.

执行以下查询以查看样本数据。

SELECT *
    FROM [AdventureWorks2017].[HumanResources].[vEmployee];

We want to split data based on the CountryRegionName column in different tables.

我们要基于不同表中的CountryRegionName列拆分数据。

• For employees belonging to the United Kingdom, insert data into the table [United Kingdom]
  对于属于英国的雇员，将数据插入表[英国]
• For employees belonging to the United States, insert data into the table [United States]
  对于属于美国的雇员，将数据插入表[美国]
• For employees belonging to Germany, insert data into the table [Germany]
  对于属于德国的雇员，将数据插入表中[德国]
• The rest of the data (not satisfying above conditions) should go to the [default] table
  其余数据（不满足上述条件）应转到[默认]表

创建一个SSIS包以根据条件拆分数据 (Create an SSIS package to split data based on conditions)

将SSIS条件拆分转换添加到数据流 (Add an SSIS Conditional Split Transformation to the Data Flow)

Let’s use the SSIS package to satisfy the above conditions and split data in multiple tables.

让我们使用SSIS包来满足上述条件并将数据拆分到多个表中。

• Open SQL Server Data Tool and go to File->New -> Project

  打开SQL Server数据工具，然后转到“文件”->“新建”-&