[SOLVED] Not Creating the File when source has 0 rows

Not Creating the File when source has 0 rows

I have the below within the Data-flow area. The problem I'm experiencing is that even if the result is 0, it is still creating the file.

Can anyone see what I'm doing wrong here?

Solution

This is pretty much expected and known annoying behavior. SSIS will create an empty flat file, even if unchecked: "column names in a first data row".

The workarounds are:

remove such file by a file system task if @RowCountWriteOff = 0 just after the execution of a dataflow.
as alternative, do not start a dataflow if expected number of rows in the source is 0:

Update 2019-02-11:

Issue I have is that I have 13 of these export to csv commands in the data flow and they are costly queries

Then double querying a source to check a row-count ahead will be even more expensive and perhaps better to reuse a value of variable @RowCountWriteOff.
Initial design has 13 dataflows, adding 13 constraints and 13 filesystem tasks the main control flow will make package more complex and harder to maintain
Therefore, suggestion is to use a OnPostExecute event handler, so cleanup logic is isolated to some certain dataflow: