Not Creating the File when source has 0 rows

Asked 10/2, 2019 at 5:40 Answered 10/2, 2019 at 9:8

Solved sql-server ssis etl flat-file flatfiledestination

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?

Irrepressible answered 10/2, 2019 at 5:40 Comment(1)

I hope that your question was answered. If so, can you consider to mark it as such one. – Macur 13/2, 2019 at 19:27

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:

Macur answered 10/2, 2019 at 9:8 Comment(8)

We have met again :) – Argentina 10/2, 2019 at 9:9

Funny, we posted the same answers at the same moment again :) – Macur 10/2, 2019 at 9:10

Thanks for that. Issue I have is that I have 13 of these export to csv commands in the data flow and they are costly queries. Any suggestions on the best approach here? – Irrepressible 10/2, 2019 at 10:48

Then just run a post-load delete of the empty file. You can hook a post-execute event with file system task. – Macur 10/2, 2019 at 10:50

Thanks for your feedback Alexander, really appreciate it, and apologies for the lack of a response. I've been down with flu the past few days and haven't been feeling well. – Irrepressible 14/2, 2019 at 10:26

Hi @AlexanderVolok - what should be contained within the "Expression Task"? – Irrepressible 15/2, 2019 at 10:38

that expressiontask is a just an entry point to make a conditional constraint to a real cleanup task. You can place in that task some dummy expression, like 1 == 1 – Macur 15/2, 2019 at 10:47

Hi @AlexanderVolok - I tried your second OnPostExecute process example, and it hasn't worked, any ideas for what I could be missing? – Irrepressible 24/2, 2019 at 6:7

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.

First you should create a Foreach Loop container to loop over tables
You should add an Execute SQL Task with a count command SELECT COunt(*) FROM ....) and store the Resultset inside a variable
Add a Data Flow Task that import data from OLEDB Source to Flat File Destination.
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:

Try to set the Data Flow Task Delay Validation property to True
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
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

Argentina answered 10/2, 2019 at 9:7 Comment(6)

Use a for eachloop contqiner to loop over queries if possible. And perform a select count sql tsak before the dataflow task – Argentina 10/2, 2019 at 11:35

If count > 0 execute the data flow – Argentina 10/2, 2019 at 11:36

@Irrepressible maybe i will update the answer with more details in a while – Argentina 10/2, 2019 at 11:37

@Irrepressible i added more details, check it out – Argentina 10/2, 2019 at 13:42

Thanks for your feedback @Hadi, really appreciate it, and apologies for the lack of a response. I've been down with flu the past few days and haven't been feeling well. – Irrepressible 14/2, 2019 at 10:26

Update 1 - Adding more details based on OP comments

Initial Answer

References and helpful links

Recommended topics

Hot tags