SSIS 2008 - "Find all references" feature for variables


It would be useful to provide an working version of VS2008's "Find All References" for a varaiable used in BIDS.
1) Select variable - from variables panel if possible or from a dialog box filtering by variable containment hierarchy
2) Show components that use that variable for example
 a)  Read-only variable list
 b) Read-write variable list
 c) Used within a property expression
 d) Used within a derived column
 e) Used within a conditional split


jm99 wrote Nov 21, 2010 at 1:15 PM


f) Used within a conditional expression within a task dependency link

furmangg wrote Jun 23, 2011 at 5:28 PM

also, highlighting unused variables as described here:

wrote Aug 31, 2011 at 3:06 PM

wrote Sep 14, 2011 at 3:59 PM

wrote Jan 5, 2012 at 9:39 AM

ValentinoVranken wrote Jan 5, 2012 at 9:48 AM

One more upvote for the "Highlight unused variables" feature!

wrote Jan 5, 2012 at 10:05 AM

wrote Jan 5, 2012 at 12:37 PM

wrote Jan 9, 2012 at 5:27 PM

wrote Feb 8, 2012 at 4:59 AM

wrote Mar 21, 2012 at 4:43 PM

wrote Aug 24, 2012 at 4:22 PM

wrote Aug 28, 2012 at 3:48 PM

wrote Oct 1, 2012 at 1:20 PM

wrote Feb 22, 2013 at 1:41 AM

wrote Apr 19, 2013 at 3:01 PM

wrote Jun 14, 2013 at 9:41 AM

wrote Sep 6, 2013 at 3:16 PM

wrote Sep 11, 2013 at 8:03 PM

wrote Sep 30, 2013 at 5:13 PM

wrote Aug 8, 2014 at 12:23 PM

wrote Sep 24, 2014 at 2:22 PM

VGDev wrote Sep 24, 2014 at 2:23 PM

Connection strings too. I have a large package and need to find each task that is using one specific connection out of multiple. A Find All would be so very helpful. Also, please apply to newer versions of SSIS (or SSDT) as well!

wrote Jan 17, 2015 at 4:14 PM

hafnera wrote Aug 17, 2015 at 3:04 PM

Up-vote from me as well! This would be VERY helpful!

DarrenSQLIS wrote Jan 29, 2016 at 1:03 PM

Shelveset now in for initial work.

Find Variable References - New button added to the variables window toolbar.

Search tasks, event handlers, and constraints, and into the data flow searches components, inputs, outputs and columns.

Check if a property has an expression, or if in data flow, if the property is tagged as being an expression (DTSCustomPropertyExpressionType.CPET_NOTIFY), then check if the expression text contains either of the following variations of a variable -
  • @Variable
  • @[Variable]
  • @[Namespace::Variable]
  • If just a property check if the value matches a literal "Namespace::Variable".
Still to do is a more detailed object type specific check, e.g. The Execute SQL Task, we need to check the Parameters collection and ResultSet, as they are complex properties.

Finally list the references found, including path, property and value matched.

Find Unused Variables - New button added to the variables window toolbar.

Search as per find references, and produce list of any variables not found.
Very limited development so far, and will add remove unused feature.

Comments welcome.

wrote Jan 29, 2016 at 1:49 PM

DarrenSQLIS wrote Mar 2, 2016 at 12:29 PM

Latest check-in is a working version of Find References and Find Unused for both Variables and Parameters. SQL2012 or greater only.

wrote Mar 2, 2016 at 12:30 PM

airrick2 wrote Jun 16, 2016 at 7:29 PM

I see the documentation says it's pre-release. Did this make it into the build? As I'm not seeing the buttons for 'Find all reference' and 'find unused'

Thanks in advance for the help!!

dgosbell wrote Jun 16, 2016 at 9:51 PM

This code was checked in after the 1.7.0 release. It will be part of the next release. There should be one shortly as we are working hard to finalize support for SQL 2016

juliakir wrote Dec 1, 2016 at 7:35 PM

will update be done for 2008 to support "Find Variable" feature ?

thank you

DarrenSQLIS wrote Dec 2, 2016 at 4:44 PM

juliakir, short answer, no. I deliberately targeted 2012+, the backward compatibility issues were become to big, although just having development machine(s) for so many versions of SQL is perhaps my biggest barrier.

Saying that, the code in the "BIDSHelper" branch could be made to work, but fixes have only gone into the new "BIDSHelper-VSIX" branch which is definitely 2012+ due to the Visual Studio charge for extensions vs add-ins.