7

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

Hadi
  • 36,233
  • 13
  • 65
  • 124
Philip
  • 2,460
  • 4
  • 27
  • 52

2 Answers2

3

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

Alexander Volok
  • 5,630
  • 3
  • 17
  • 33
  • 1
    We have met again :) – Hadi Feb 10 '19 at 09:09
  • 1
    Funny, we posted the same answers at the same moment again :) – Alexander Volok Feb 10 '19 at 09: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? – Philip Feb 10 '19 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. – Alexander Volok Feb 10 '19 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. – Philip Feb 14 '19 at 10:26
  • Hi @AlexanderVolok - what should be contained within the "Expression Task"? – Philip Feb 15 '19 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` – Alexander Volok Feb 15 '19 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? – Philip Feb 24 '19 at 06:07
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

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • 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? – Philip Feb 10 '19 at 10:48
  • Use a for eachloop contqiner to loop over queries if possible. And perform a select count sql tsak before the dataflow task – Hadi Feb 10 '19 at 11:35
  • If count > 0 execute the data flow – Hadi Feb 10 '19 at 11:36
  • @Philip maybe i will update the answer with more details in a while – Hadi Feb 10 '19 at 11:37
  • @Philip i added more details, check it out – Hadi Feb 10 '19 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. – Philip Feb 14 '19 at 10:26