I'm using a simple data flow to extract rows from a table (using a SQL query) and put them in a flat file. If the query returns no rows, I don't want a file to be created. Right now it creates a file with the headers (since I do want the headers if there is data).
Any one know how to do this?
Kevin
Kevin,
Unfortunately I don't think there is any way around this. The workaround is to check to see if any rows were inserted (put a ROWCOUNT component immediately prior to the flat file destination adapter). If NumberOfRows=0, delete the resultant file.
-Jamie
|||
I did get it to work as Jamie suggests. For others who are trying to do the same, I'll post my solution below.
* At the Control Flow level, create a Sequence Container
* Place a Data Flow task in the Container
* Create a variable (called RowCounter of type Int32)
* Within the Data Flow Task, I have four objects in this order: 1) a Data Flow Source (such as OLE DB), 2) a Transformation (such as Copy Column), 3) the Row Count Transformation, and 4) a Data Flow Destination (such as Flat File).
* The Row Count Transformation is set up with the variable RowCounter
* Now back at the Control Flow level, place a File System Task in the Sequence Container
* Connect the Precedence Constraint from the Data Flow to the File System Task
* Open the properties of the Precedence Constraint and set the Evaluation Operator to "Expression and Constraint", the Value to "Success", and the Expression to "RowCounter == 0"
* Finally set the properties of the File System Task as follows: Operation = "Delete File" and Source Connection to the name of the File Connection that you used in the Data Flow Task.
