One of the difficult aspects of SSIS package development is keeping track of where a variable has been used in an expression or whether a variable is being used at all. If you’re developing your packages with BI xPress, you can easily discover where your variables are being used if they’re being used at all.

Open your package in BIDS, and go to the Variables windows. Highlight the variables you want to check for dependencies and then click the Scan Variable Dependency icon.

This will open the Variable Scanning Options. You can specify to scan all variables, which could take a considerable amount of time. In my case, I’m going to choose to scan for only the variables I have selected.

When the Variable Usage windows opens up, click the Scan all objects for variable usage button at the bottom. This is going to actually search our package to discover if and where the variables could be used.

Once the scan is complete, we can see on the right side of the window the tasks where our variables are used. In my case, my selected variable is used in an Execute SQL Task and a Script Task. If I highlight the Script Task, I can even view the script and see where the variable is used.

Any variables that are not being used in the package will show up under the Unused Variables node. At the bottom of the window you’ll notice that we have the option to either rename or delete these unused variables.

Head to PragmaticWorks.com to checkout BI xPress and download the free trial version.