Don’t Forget the Parameter

This is just a quick post to remind myself as much as anyone else who’s interested…

I am a firm believer in “Develop Once…Copy Many” since as far as I’m concerned, the fun in development is in the problem-solving, not necessarily in multiple implementations of a solution (no matter how cool it happens to be). 

Over time, I’ve evolved a templated “cookie-cutter” approach to ETL that allows me to copy and reuse SSIS containers for data mart object processing.  Normally, this works quite well, although BIDS has some interesting ideas about how to arrange nested containers and objects, which necessitates some fancy footwork at times. 

One of the instances (other than display) in which this fails is in copying a Data Flow task with an OLE DB Source adapter that includes a parameter in a SQL command.  When copying the the data flow task, the placeholder for the parameter is persisted, but the variable mapping for the parameter is not:

So, if you’re seeking the path of least resistance in your SSIS development and choose to copy and re-use Data Flow tasks that include parameters mapped in the SQL statement of an OLE DB Source adapter, don’t forget to remap the variable.