MVP Matthew Roche blogged the other day about the joys of discovering some useful but little-known design-time options for Integration Services in the Options dialog box for Visual Studio/Business Intelligence Development Studio. I suspect that many of us haven't looked closely at all the options in VS that affect our design-time experience.
We tried to summarize everything that's SSIS-specific in this Books Online topic: Integration Services in Business Intelligence Development Studio.
But let's do a quick recap right here today with screen shots.
Visual Studio Settings: "One profile to rule them all..."
These options affect the Visual Studio menus, toolbars, and windows.
Quick, who remembers seeing this dialog box the first time you ran Visual Studio? OK, now, who remembers what they picked?
I always waffle...I do SSIS, I do VB and C#, and I play with some Web stuff. But it makes a difference! My fellow writer, Carla, and I have had more than one "who's on first" conversation about VS menus, because we chose different profiles and ended up with different menus. "How did you do that?" "I just clicked X on the Y menu." "I don't have a Y menu." "You must have one...I do!" "I told you, I don't have a Y menu." And that's the least of what Carla has to put up with from me! But I digress.
There seems to be no way to check which profile is active. In any case, you can always clean things up, or make all your workstations consistent, by using the Import and Export Settings Wizard on the Tools menu to reset your VS configuration to one of the default profiles.
SSIS Designer options under Tools / Options
These options affect the design of Integration Services packages in every SSIS project and package that you create.
Have fun exploring! The following screen shots are from a recent build of SQL Server 2008.
Options screen 1 of 4: General
- Even when we check digital signatures, we don't check whether the package has been changed. (See improved topics on digital signing in SQL Server 2008 BOL, including Using Digital Signatures with Packages.)
- Default scripting language is now C#.
- Note the options for automated upgrade of SQL Server 2005 Integration Services packages when you open them in the designer. For more information, see Upgrading Integration Services Packages,
Options screen 2 of 4: Delete Confirmation
Options screen 3 of 4: Control Flow Auto Connect
Options screen 4 of 4: Data Flow Auto Connect
Integration Services Project Properties
These options affect the select Integration Services project.
Project Properties screen 1 of 3: Build
"Build" doesn't really do much for an Integration Services project, other than (re)build your deployment utility, if you've chosen to create one.
Project Properties screen 2 of 3: Deployment Utility
For more information, see Deploying Packages by Using the Deployment Utility.
Project Properties screen 3 of 3: Debugging
A couple items of interest here:
- RunInOptimizedMode. This property has the same name as a property of individual data flow tasks; here, it applies only at debugging time, and overrides the property settings of individual data flow tasks. This useful property optimizes your data flow in advance, especially by removing unused columns.
- Run64BitRuntime. On a 64-bit computer, by default, packages run in 64-bit mode in BIDS. This property is simply ignored on a 32-bit computer. If you're developing on a 64-bit computer and have to run the package in 32-bit mode to use a 32-bit-only feature (like the Excel driver), change this value to False.
Extra bonus! Watching package variables while debugging
Still with me? Then here's something valuable that I myself didn't learn until embarrassingly late in the SQL Server 2005 era: You can watch the changing values of your Integration Services package variables in the Locals window of Visual Studio. You have to set a breakpoint on something to pause the package before you can look at them. You can also add individual variables to the Watch window, for a less cluttered list, by selecting one in the Locals window and picking Add Watch from the context menu, or simply by typing the variable name in the Watch window list while debugging is paused.
I hope that you enjoyed this quick tour and learned some new things!