SQL Server Business Intelligence Development Studio (BIDS) provides a powerful environment for developing SQL Server Integration Services (SSIS)
packages. Once you get the hang of how to implement tasks in your control flow and configure your data flow, most efforts are fairly straightforward.
Even so, you can sometimes run into situations that slow down development or stop progress altogether. Often these situations are easy to resolve if
you know how to proceed. Here are five tips to help you avoid a few frustrations that might arise when developing SSIS packages so that you can keep
your development efforts moving forward.
Tip #1: Control Design Time Validation
When you open an SSIS package in BIDS, SSIS Designer tries to connect to the defined data sources and verify the metadata associated with those data
sources. If the data sources are unavailable, the database objects are locked, or the connect times are slow, you might have to wait an inordinate
amount of time for the validation to complete or to learn that the validation failed. This can be particularly annoying if you have to reopen your
packages often.
You can also run into validation problems if you try to add a component that relies on a database object not yet created. For example, suppose a
control flow includes an Execute SQL task that creates a table. If you add a Data Flow task that populates the table with data, you'll receive a
validation warning because SSIS can't validate the table -- it doesn't yet exist. And if you try to run the package, you'll receive a validation error.
Fortunately, SSIS lets you override the default behavior by allowing you to work offline or to configure properties that affect validation. To work
offline, you simply enable the Work Offline option on the SSIS menu. You can access this option while you're working on the package or before you open
it. (To do the latter, a different BIDS solution must be open so that the SSIS menu is displayed.) When you're ready for your package to be back
online, you just disable the Work Offline option, and SSIS Designer returns to its default behavior.
The second method you can use to override the default validation behavior is to set one of the two validation-related properties in your package.
First, you can set the DelayValidation property to True for a specific task. For instance, Figure 1 shows the property set to True for a Data Flow
task. After you've configured the property, you can work with the task and run your package without receiving those validation warnings or errors.
Figure 1: Setting the DelayValidation property on a Data Flow task
Note, however, that the DelayValidation property is available only in executables (e.g., Control Flow tasks, the package itself). The property isn't
available in data flow components. If you want to change the behavior of a specific data flow component, you must set its ValidateExternalMetadata
property to False. Figure 2 shows the property set to False for an OLE DB Source component in the data flow.
Figure 2: Setting the ValidateExternalMetadata property on an OLE DB Source component
Setting the ValidateExternalMetadata property to False provides more granular control over your data flow than setting the DelayValidation property to
True. However, when the ValidateExternalMetadata property is set to True (the default), it helps avoid locking issues when your package uses
transactions. For this reason, you might want to stick with using DelayValidation at the task level, unless you have some overriding need to work at
the component level.
Tip #2: Create a Package Template
For many SSIS developers, it's not uncommon to create multiple packages that contain many of the same components. Your packages might share variables,
connection managers, event handlers, property settings, or tasks. Rather than implementing these same components each time you create a package, you
can create a package template that includes all these components and base each new package on the template.
Although creating a template in BIDS is relatively simple, it's not particularly intuitive how you go about doing it. Basically, you create a package
in SSIS Designer as you would any other package, although you should give it a name that makes it easily recognizable as a template, such as
SsisPkgTemplate.
After you've added the components that you want to include in the template, save and close the package. Then go to the Windows Explorer folder where
you store your SSIS project files and copy the template package you just created to the BIDS template folder. The location of the template folder can
vary depending on the OS, whether you're working in a 32-bit or 64-bit environment, and whether you've installed BIDS or Visual Studio in its default
location. On my system, I'm running 64-bit versions of Windows 7 and SQL Server 2008 R2, both installed on the C drive, with BIDS installed in its
default location. As a result, the location of my template folder is C:\Program Files\Microsoft Visual Studio
9.0\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems.