I have text file that contains data for a given fiscal period. In this particular file there are around 2 million records. The file needs to be split into two distinct output paths from the same source, which means this should all happen within a given DataFlow component. One of the paths simply sums the data via a specific set of columns, nothing unusual. The other path required that if the fiscal period was not valid, then no data was to be written. This sounded simple enough, except the validation routine was a stored procedure.
The stored procedure is a very powerful tool and if you follow a few rules, it will perform very fast and efficiently.
Rule 1: Do not change parameters that are SARGS.
Rule2: If you must break Rule 1, split your procedures.
Let's create a procedure to use in our examples: