Wednesday, July 18, 2012

Unable to add/delete/view existing variables in SQL Server data tools (formerly BIDS) after package upgrade

Short Description:  The Add Variable button is greyed out in SQL Server Data Tools, in my case this was happening with packages upgraded from 2008 R2 to 2012.

I was running into an issue where I was unable to view or add variables in the Variables pane after upgrading packages from 2008 R2.  Everything except the Grid Options button was greyed out.  Eventually the ability would "magically" re-appear along with the existing variables, but the behavior was frustrating and a time-sink.

Workaround:  Unfortunately, I don't know what causes this (other than upgrading a package) or how common the problem is, but right clicking the design surface and selecting variables rather than opening the variables pane appears to force it to refresh and behave itself again.  It appears to only need to be refreshed once per package.