• Article

Behavior Changes to Integration Services Features in SQL Server 2008 R2

This topic describes behavior changes in Integration Services. Behavior changes affect how features work or interact in SQL Server 2008 compared to earlier versions of SQL Server.

Note

For SQL Server 2008 R2, there have been no changes to the content that is listed in this topic.

Behavior Changes for the Execute SQL Task

The following table describes the behavior changes for the Execute SQL task.

Issue

SQL Server 2005 Integration Services Behavior

SQL Server 2008 Integration Services Behavior

Returning a NULL value in a package variable from the Execute SQL task

Returns the value as 11/30/1999.

Returns the value as 12/30/1899.

Behavior Changes for the Lookup Transformation

The following table describes the behavior changes for the Lookup transformation.

Issue

SQL Server 2005 Integration Services Behavior

SQL Server 2008 Integration Services Behavior

Using the Lookup transformation

Cannot persist the cached reference table or share this table among multiple Lookup transformations.

Can persist the cached reference table or share this table among multiple Lookup transformations.

To use the new features of the Lookup transformation, consider revising your packages.

Behavior Changes for the Script Task and Script Component

The following table describes the behavior changes for the Script task and the Script component.

Issue

SQL Server 2005 Integration Services Behavior

SQL Server 2008 Integration Services Behavior

Developing scripts in the Script task and Script component

Uses Visual Studio for Applications (VSA) as the scripting environment.

Uses Visual Studio Tools for Applications (VSTA) as the scripting environment.

To migrate packages that contain scripts, use the SSIS Package Upgrade Wizard.

When you use the dtexec utility (dtexec.exe) to run a deployed package, the sequence in which events occur has changed.

In SQL Server 2005 Integration Services, events occur in the following order:

  1. The utility first applies the design-time configurations. (Design-time configurations are the configurations that you created and associated with the package when you designed the package in BI Development Studio.)

  2. The utility then applies the run-time options that you specified on the command line when you started the utility.

In SQL Server 2008 Integration Services, events occur in the following order:

  1. The utility first applies the design-time configurations.

  2. The utility then applies the run-time options that you specified on the command line when you started the utility.

  3. Finally, the utility reloads and reapplies the design-time configurations.

Because the events occur in this order, you can dynamically redirect a configuration by using the /SET command-line option to change the configuration string.

The following table examines the command-line options that you can be use to affect configurations, and compares the behavior for these options in SQL Server 2005 and SQL Server 2008.

Dtexec option and usage

Behavior in SQL Server Integration Services 2005

Behavior in SQL Server Integration Services 2008

Using the /Connection option to change the connection string that the utility uses to load design-time configurations

Has no effect.

After the utility applies the run-time options, the utility does not reload design-time configurations.

Succeeds.

After the utility applies the run-time options, the utility reloads the design-time configurations from the new location.

Using the /Set option to change the location from which the utility loads design-time configurations

Has no effect.

After the utility applies the run-time options, the utility does not reload the design-time configurations.

Succeeds.

After the utility applies the run-time options, the utility reloads the design-time configurations from the new location.

Using the /Set option to change a property value that is also set by a design-time configuration

Succeeds.

The new value is not overwritten because the utility does not reload design-time configurations after it applies the run-time options.

Has no effect.

The new value is overwritten when the utility reloads the design-time configurations after it applies the run-time options.

Using the /ConfigFile option to load run-time configurations for the same property values that are also set by design-time configurations

Succeeds.

The new values are not overwritten because the utility does not reload design-time configurations after it applies the run-time options.

Has no effect.

The new values are overwritten when the utility reloads the design-time configurations after it applies the run-time options.

Using the /ConfigFile option to load run-time configurations for different property values than those set by design-time configurations

Succeeds.

Succeeds.

For more information, see SSIS Package Configurations and dtexec Utility (SSIS Tool).