SSIS 中的平面文件源错误输出连接 [英] Flat File source Error Output connection in SSIS

问题描述

我在 SSIS 中有一个平面文件源连接.我为错误输出创建了一个平面文件目标连接.我已经用红色箭头连接了它.问题是即使平面文件源中没有错误，也会创建错误输出文件.即使没有错误，错误平面文件目的地"也始终变为绿色.如何解决.提前致谢

I have a flat file source connection in SSIS. I have created one flat file destination connection for error output. I have connected it with red arrow. The problem is that the error output file is created even if there is no error in the flat file source. The "Error Flat file Destination" always turns green even if no error. How to solve it . Thanks in advance

推荐答案

可能，您已经找到了解决方案.我希望这可以帮助寻找解决此问题的方法的人.

Probably, you have already found a solution. I hope this might help someone looking for a solution to this problem.

SSIS 中没有可用的内置数据流任务设置来执行此操作.您可以使用数据流任务中的Row Count 转换结合控制流上的File System Task 来实现这一点.

There is no in-built data flow task setting available in SSIS to do this. You can use Row Count transformation inside Data Flow task combined with File System Task on the Control Flow to achieve this.

以下是有关如何执行此操作的分步过程.在此示例中，我使用了一个名为 Country_State.csv 的 csv 文件，其中包含国家和州作为源文件.

Here is step-by-step process on how to do this. In this example, I have used a csv file named Country_State.csv containing countries and states as the source file.

场景:

示例包将读取该文件，然后写入名为 Destination.txt 的文本文件.在这种情况下，错误文件 Error.txt 将被创建，但如果没有错误，稍后将被删除.在这里，我将文件存储在路径 c:\temp\

The sample package will read the file and then write to a text file named Destination.txt. In this scenario, the error file Error.txt will be created but later deleted if there are no errors. Here, I have the files stored in the path c:\temp\

分步流程:

1. 在连接管理器部分，创建三个平面文件连接，即源、目标和错误.请参阅屏幕截图 #1.
2. Source 连接应指向 csv 文件路径 c:\temp\Country_State.csv.有关此文件的内容，请参阅屏幕截图 #2.
3. Destination 连接应该指向一个名为 c:\temp\Destination.txt 的文本文件.
4. Error 连接应该指向一个名为 c:\temp\Error.txt 的文本文件.
5. 创建一个名为 ErrorCount 的数据类型为 Int32 的变量.
6. 在控制流选项卡上，放置一个数据流任务，然后放置一个文件系统任务.
7. 将数据流任务连接到文件系统任务.
8. 右键单击数据流任务和文件系统任务之间的连接器.
9. 在优先约束编辑器上，将Evaluation operation更改为Expression并粘贴值@ErrorCount == 0表达式 文本框中的代码>.
10. 您的控制流应该如屏幕截图 #3 所示.
11. 在数据流选项卡上的数据流任务中，拖放一个平面文件源并将其配置为使用Source 连接管理器.
12. 放置一个平面文件目标并将其配置为使用目标连接管理器.
13. 将绿色输出箭头从平面文件源连接到平面文件目标.
14. 在数据流选项卡上放置一个 Row Count 转换并将其配置为使用变量 User:ErrorCount.
15. 将红色输出箭头从平面文件源连接到行计数转换.
16. 放置一个平面文件目标并将其配置为使用 Error 连接管理器.
17. 使用 Error 连接将 Row Count 的输出连接到平面文件目标.
18. 您的数据流任务应如屏幕截图 #4 所示.
19. 在控制流选项卡上，双击文件系统任务.
20. 在文件系统任务编辑器上，将 Operation 设置为 Delete file 并将 SourceConnection 设置为 Error.请参阅屏幕截图 #5.
21. 包执行前文件夹路径 C:\temp 的内容显示在屏幕截图 #6 中.
22. 数据流选项卡执行如屏幕截图 #7 所示.
23. 控制流执行如屏幕截图 #8 所示.
24. 包执行后文件夹路径C:\temp的内容如截图#9所示.
25. 为了显示这确实有效，我将 Source 连接管理器上的第二列更改为整数(即​​使状态名称是字符串)，以便数据流任务重定向到 Error 输出.
26. 场景 2 数据流选项卡执行如屏幕截图 #10 所示.
27. 场景2 控制流执行如屏幕截图 #11 所示.请注意，文件系统任务没有执行，因为错误文件不为空.
28. 场景2包执行后文件夹路径C:\temp的内容如截图#12所示.请注意，即使没有成功的行，文件 Destination.txt 也存在.这是因为该示例仅删除空的错误文件.

1. On the connection manager section, create three flat file connections namely Source, Destination and Error. Refer screenshot #1.
2. Source connection should point to the csv file path c:\temp\Country_State.csv. Refer screenshot #2 for the contents of this file.
3. Destination connection should point to a text file named c:\temp\Destination.txt.
4. Error connection should point to a text file named c:\temp\Error.txt.
5. Create a variable of data type Int32 named ErrorCount.
6. On the Control Flow tab, place a Data Flow Task and then place a File System Task.
7. Connect the Data Flow Task to File System Task.
8. Right click on the connector between Data Flow Task and File System Task.
9. On the Precedence Constraint Editor, change the Evaluation operation to Expression and paste the value @ErrorCount == 0 in the Expression textbox.
10. Your control flow should look like as shown in screenshot #3.
11. Inside the data flow task on the data flow tab, drag and drop a Flat File Source and configure it to use Source connection manager.
12. Place a Flat File Destination and configure it to use Destination connection manager.
13. Connect the green output arrow from the Flat File Source to the Flat File Destination.
14. Place a Row Count transformation on the data flow tab and configure it to use the Variable User:ErrorCount.
15. Connect the red output arrow from the Flat File Source to the Row Count transformation.
16. Place a Flat File Destination and configure it to use Error connection manager.
17. Connect the output from Row Count to the Flat File Destination using Error connection.
18. Your data flow task should look like as shown in screenshot #4.
19. On the Control Flow tab, double-click on the File System Task.
20. On the File System Task Editor, set the Operation to Delete file and set the SourceConnection to Error. Refer screenshot #5.
21. Contents of the folder path C:\temp before package execution are shown in screenshot #6.
22. Data flow tab execution is shown in screenshot #7.
23. Control flow execution is shown in screenshot #8.
24. Contents of the folder path C:\temp after package execution are shown in screenshot #9.
25. To show this actually works, I changed the second column on the Source connection manager to integer (even though state names are strings) so that the data flow task redirects to the Error output.
26. Scenario 2 Data flow tab execution is shown in screenshot #10.
27. Scenario 2 Control flow execution is shown in screenshot #11. Notice that the File System Task is not executed because the error file is not empty.
28. Contents of the folder path C:\temp after Scenario 2 package execution are shown in screenshot #12. Notice that the file Destination.txt is present even though there were no successful rows. This is because the example deletes only the Error file if it is empty.

类似的逻辑可用于删除一个空的目标文件.

Similar logic can be used to delete a empty Destination file.

希望有所帮助.

屏幕截图 #1:

屏幕截图 #2:

屏幕截图 #3:

屏幕截图 #4:

屏幕截图 #5:

屏幕截图 #6:

屏幕截图 #7:

截图 #8:

屏幕截图 #9:

屏幕截图 #10:

屏幕截图 #11:

屏幕截图 #12:

这篇关于SSIS 中的平面文件源错误输出连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！