If-then-else logic in an SSIS package? 1

[SQLHacker]

Using SQL Server 2005 SSIS. I have created a package that imports a flat file, does some conversions and adds a couple of derived columns and then sends the output to a SQL Server table (works great). Now...I want to add logic in a step before the OLE DB Destination step that determines the number of rows processed and if the number is "X" proceed to the OLE DB Destination step, but if the number is "Y", stop processing.

Another potential problem is the data does not have a unique identifier (all string fields) to do a count (distinct or not) on the number of rows, but that may not be an issue.

Any suggestions are appreciated.

 

[MDXer]

This should work.

1)Create a rowcount variable you need to set the RaiseChangedEvent to true.
2) Prior to your Destination put a RowCount task and assign the count to yuor variable.
3) Change your Destination to a RawFile
4) New Data flow that utilizes your raw file as a source and dumps direct to your destination.
5) Work flow between your 2 dataflows needs to Evaluate as a constraint. Make the expression @reccountvar >= MyVal

now you could enhance this by making yVal a variable that is configurable at runTime. If the Count is Less than MyVar the process will not continue to the next dataflow. If you have additional dataflows after these 2 you need to make OR Success work flows to the next process in the tree.


Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck