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. |
Behavior Changes Related to Package Configurations
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:
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.)
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:
The utility first applies the design-time configurations.
The utility then applies the run-time options that you specified on the command line when you started the utility.
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).