November 15, 2010 at 9:48 am
SQL Server 2008
My package generates a table of data and then exports it to disk. I only want the export to happen if there are data in the table - i.e. I do not want to produce an empty file. There's an Execute SQL task after the export that I don't want to run either. I know I could test for the presence of data within an Execute SQL task and call RAISEERROR if the table was empty, but don't want to have errors showing up anywhere if I don't have to. How do I implement this kind of control flow in SSIS?
Thanks
Scott
--
Scott
November 15, 2010 at 9:59 am
I would recommend getting the row count from your table and populating a variable with it. Use that variable inside a precedence constraint. Use an expression to test for a non zero value in an expression on the precedence constraint.
If you've used a Data Flow to populate your table, simply use the Row Count transformation just before your destination adapter to get the row count into a variable. If you've not used a Data Flow, you'll need to call another ExecuteSQL task and return the results into a variable from there. Either way, it's pretty straight forward.
November 15, 2010 at 10:07 am
I hadn't known about expressions in constraints until now. It is straightforward, isn't it?
Thanks
--
Scott
November 15, 2010 at 10:15 am
Yes. In your Control Flow, just double-click on the connector and it will bring up the Precedence Constraint Editor. For the Evaluation Operation, you'll want to use Expression and Constraint. Set the Value option to Success and build your Expression in the Expression box.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply