Search code examples
sql-serverssisetlflat-fileflatfiledestination

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?

enter image description here

enter image description here

enter image description 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: enter image description here

    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:

    enter image description here