ssis to stop generating flat file with 0kb output

Question

Hi there

I have tried all the methods to stop generating flat file with 0kb output but it doesnt work.

The method I found was

1. take row count transformation and use conditional split to check if any rows exist or not ,if it does exist then create the flat file destination, otherwise dont.
2. I also saw that we could use script task to get this done, but as i am new to SSIS I am not sure which is feasible and optimal.

The other thing to highlight is that I am using visual studio 2008, so not sure how this can be handled in the earlier version.

My SSIS is flat file source to flat file destination with several transformation used. So I am generating error output in case of no match output.But even if their is no error an output error file is generated.

Any suggestions

Regards
Farhan Jamil

Answer 1

You can try approach outlined here:
how-to-prevent-ssis-package-creating-empty-flat-file-at-the-destination

Answer 2

Hi @Farhan Jamil ,

The Row Count transformation stores the row count value in the variable only after the last row has passed through the transformation.
Therefore, the value of the variable is not updated in time to use the updated value in the data flow that contains the Row Count transformation.
You can use the updated variable in a separate data flow.

We should create 2 Data Flow Tasks.

1. We can use Row Count transformation to store the row count value in Data Flow Task1.
2. Then we can use the variable @RowCount in the Conditional Split transformation in Data Flow Task2.

Please refer to the following pictures:

Best Regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues in November--What can I do if my transaction log is full?
Hot issues in November--How to convert Profiler trace into a SQL Server table?