SSIS - 根据第一列值从SQL表导出到多个平面文件 [英] SSIS - export from SQL table to multiple flat files based on first column values

问题描述

以下是SQL Table的一个示例：

Here is an example of what the SQL Table looks like:

Name    Class   Grade
Jesse   English A
Jesse   Math    C
Jesse   History A
Scott   Math    B
Scott   History B
Scott   English A
Mike    History A
Mike    English D

我正在尝试让SSIS为每个人动态创建一个平面文件。示例：

I am trying to get SSIS to dynamically create a flat file for each person. Example:

平面文件名称： Jesse

Name    Class   Grade
Jesse   English A
Jesse   Math    C
Jesse   History A

平面文件名称： Scott

Name    Class   Grade
Scott   Math    B
Scott   History B
Scott   English A

平面文件名：强>迈克

Flat File name: Mike

Name    Class   Grade
Mike    History A
Mike    English D

我可以轻松地在sql表和平面文件之间创建一个静态链接，但是我打算添加很多人们到桌子，否则会使我为每个人创建一个数据流任务。这不太理想我希望为每个循环识别名称列中的不同值，然后将合格的行输出到平面文件中。

I can easily create a static link between the sql table and the flat file but I plan on adding a lot of people to the table which would otherwise cause me to create a data flow task for each. This would not be ideal. I was hoping for a for each loop that identified the distinct values within the Name column and then output the qualified rows into a flat file.

推荐答案

首先运行查询以查找独特学生的记录集：

First run a query to find a recordset of unique students:

select distinct name from myTable

然后使用foreach循环循环并运行以下参数化查询：

Then use the foreach loop to loop though and run the following parameterized query:

SELECT class, grade 
FROM myTable 
WHERE name = ? 

使用派生列将名称添加到结果集。

Use a derived column to include the name to the resultset.

将其放在平面文件目的地。输出文件的连接字符串将是动态的。

Put this in a flat file destination. The connectionstring for the output file will be dynamic.

这些是步骤。如果你被卡住了，网上有很多例子，或者随便问。

These are the steps. If you get stuck, there are plenty of examples online, or feel free to ask.

这篇关于SSIS - 根据第一列值从SQL表导出到多个平面文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！