Mar
16
2009

Solve some common pain points in BIDS

Here are a few suggestions for some of the things that may be tripping you up while you're working on SQL Server Integration Services packages in Business Intelligence Development Studio (BIDS). Thanks to my buddy Jason Howell, who's an SSIS whiz in Microsoft Support, for sending us this list recently.

1. Make that "Visual Studio is busy" message go away!

Validation happens often in BIDS and can be slow, especially if you're connecting to slow or unavailable data sources.

Possible solutions:

  • "Work Offline" on the SSIS menu.
  • "DelayValidation" property of SSIS objects.

More information: Troubleshooting Package Development

2. My package won't work in 64-bit mode. How do I debug it in 32-bit mode?

Solution: There's a somewhat hidden design-time setting, "Run64BitRuntime", which is True by default.

More information:  Debug your packages in 32-bit or 64-bit mode

3. I tried to save a large package and lost the whole thing.

More information and suggested workaround: Microsoft Knowledge Base article 952110

4. Where did all my Visual Studio menu options go? I'm trying to write code and can't find half the things I need.

Depending on the order in which you installed SQL Server and Visual Studio, we may have performed the helpful step on your behalf of replacing your Development settings in BIDS with the default set of BI settings.

More information with screen shots at the beginning of this blog post: Profiles, Settings, and More Settings

5. I don't know where I used SSIS Expressions.

This is particularly misleading in BIDS. You may still see the original hard-coded value that you supplied for a given property, but you DON'T see (without digging) the expression that you wrote that's replacing that value when you run the package.

Alternative: Use the BIDS Helper tool available on Codeplex. BIDS Helper has 2 features that indicate which tasks and connection managers, or which package variables, have properties to which expressions have been applied. Here's a quick glance at one of those features...

More information: See the 2nd of the 4 BIDS Helper features described in this blog post: Add helpful package design tools to BIDS: A Codeplex project focus

6. I can't tell what's performing well and what's not in my Data Flow.

The information on the Progress tab in BIDS doesn't make it easy to visualize what's taking a long time.

Possible solutions for more performance information:

Alternative: Use the BIDS Helper tool available on Codeplex. BIDS Helper has at least 2 features that help to display and visualize the performance of the package and the data flow. Here's a quick glance at the feature that's specifically for the data flow, although the other feature has prettier charts...

More information: See the 3rd of the 4 BIDS Helper features described in this blog post: Add helpful package design tools to BIDS: A Codeplex project focus


I hope that these suggestions are helpful!

Please comment on my blog!

You don't have to sign up or sign in - anonymous comments are enabled. I "moderate" the comments to delete spam before you see it, but otherwise, I publish your comments immediately. You can also email me at dougbert@dougbert.com. Thank you!

-Doug

Comments (2) -

Ravi G

Nice post.


I like #5 very much, especially helpful to identify variables that use expressions.


We were planning to use a naming convention to identify a variable with expression. Probably still a good idea.


I'm checking out BIDS Helper. Thanks.


PatLim

Bids helper hint is very nice.


i've always wonder why we could change the scope of a variable.


Now with bids helper it's possible.


Thank