Not Creating the File when source has 0 rows

Question

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?

Answer 1

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:

Answer 2

Update 1 - Adding more details based on OP comments

Based on your comment i will assume that you want to loop over many tables using SQL Commands, check if table contains row, if so then you should export rows to flat files, else you should ignore the tables. I will mention the steps that you need to achieve that and provide links that contains more details for each step.

1. First you should create a Foreach Loop container to loop over tables
2. You should add an Execute SQL Task with a count command SELECT COunt(*) FROM ....) and store the Resultset inside a variable
3. Add a Data Flow Task that import data from OLEDB Source to Flat File Destination.
4. After that you should add a precedence constraint with expression, to the Data Flow Task, with expression similar to @[User::RowCount] > 0

Also, it is good to check the links i provided because they contains a lot of useful informations and step by step guides.

---

Initial Answer

Preventing SSIS from creating empty flat files is a common issue that you can find a lot of references online, there are many workarounds suggested and many methods that may solves the issue:

1. Try to set the Data Flow Task Delay Validation property to True
2. Create another Data Flow Task within the package, which will be used only to count rows in the Source, if it is bigger than 0 then the precedence constraint should led to the other Data Flow Task
3. Add a File System Task after the Data Flow Task which delete the output file if RowCount is o, you should set the precedence constraint expression to ensure that.

References and helpful links

• How to prevent SSIS package creating empty flat file at the destination
• Prevent SSIS from creating an empty flat file
• Eliminating Empty Output Files in SSIS
• Prevent SSIS for creating an empty csv file at destination
• Check for number of rows returned and do not create empty destination file
• Set the Data Flow Task Delay Validation property to True