Flat File source Error Output connection in SSIS

3
votes

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

1
ssis

1 Answers

13
votes

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

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.

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.

Scenario:

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\

Step by step flow:

  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.

Hope that helps.

Screenshot #1:

1

Screenshot #2:

2

Screenshot #3:

3

Screenshot #4:

4

Screenshot #5:

5

Screenshot #6:

6

Screenshot #7:

7

Screenshot #8:

8

Screenshot #9:

9

Screenshot #10:

10

Screenshot #11:

11

Screenshot #12:

12