You may be unable to execute SQL Server 2005 Integration Services packages that contain script tasks or script components

In Microsoft SQL Server 2005, you may be unable to execute SQL Server 2005 Integration Services (SSIS) packages that contain script tasks or script components. Additionally, the scripts may not function correctly when you try to edit the scripts. This issue may occur on computers that are running one of the following operating systems:

• Windows Vista
• A Windows operating system that has had a Microsoft .NET Framework 2.0 hotfix later than build 2.0.50727.832 installed

For an overview of how these components interoperate, see the "More information" section.

For example, this issue occurs if the following conditions are true:

• You install the SSIS development tools on a Windows Vista-based computer. Alternatively, you install the SSIS development tools on a computer that is running a Windows operating system that has had a .NET Framework hotfix later than build 2.0.50727.832 installed.
• You use SQL Server Business Intelligence Development Studio to create or edit an SSIS package.
• In the SSIS package, you try to create or edit a script task or a script component.
• You try to run the package on the computer or any other computer that has a similar configuration.

In this example, when you try to save the script changes, the script editor may behave incorrectly or may generate error messages. Additionally, the SSIS engine may be unable to execute the package that contains the scripts. However, you may be able to run or edit the same package successfully on a computer that is not running Windows Vista or that has not had a .NET Framework 2.0 hotfix later than build 2.0.50727.832 installed.

When this problem occurs, you may experience the following symptoms:

• When you try to execute an existing SSIS package that contains a script task, the execution may fail, and you receive the following error message:
  Script files failed to load
• When you try to execute an existing SSIS package that contains a script component, the execution may fail with a "NullReferenceException" error message.
• When you create a new script or edit an existing script after you have closed the Microsoft Visual Studio for Applications (VSA) scripting environment, you may receive the following error message:
  The task/component is configured to precompile the script, but binary code is not found
• In a script task or in a script component, you set a breakpoint in the code. When you debug the script task or the script component, you may receive the following error message:
  The script files failed to load
  Warning: 0x8001902
  An exception of type 'System.Runtime.InteropServices.COMException' occurred in Microsoft.VisualBasic.Vsa.dll but was not handled in user code
  Additional information: Retrieving the COM class factory for component with CLSID {A138CF39-2CAE-42C2-ADB3-022658D79F2F} failed due to the following error: 80040154.
  Note To resolve this problem, install the hotfix that is described in the following Microsoft Knowledge Base article if the version of the .NET Framework 2.0 is earlier than build 2.0.50727.832.
  928208 FIX: Custom applications that use the Visual Studio for Applications Visual Basic Runtime may be unable to compile macros or to run macros

Note You experience these symptoms regardless of whether SQL Server 2005 Service Pack 1 (SP1) is installed.

This problem occurs because of a recent change in the common language runtime (CLR) workstation runtime (Mscorwks.dll) for builds of the Microsoft .NET Framework 2.0 later than 2.0.50727.832. The change introduces a binary-compatibility problem for the Microsoft Visual Basic runtime engine (Vsavb7rt.dll) that VSA and SSIS use. The problem prevents assemblies from generating correctly during the macro compilation operation. This problem causes existing packages that contain compiled scripts not to execute.

Certain design-time operations of VSA are also affected. For example, the compiler may be unable to generate Common Intermediate Language (CIL) when you use VSA at design time.

To resolve this problem, use one of the following methods based on your need.

Note The following methods describe hotfixes that you can apply on the computer that is running SSIS. Notice that each of these hotfixes provides the same functionality. The only difference is that each hotfix is packaged according a particular version of SQL Server 2005.

Method 1

Note We recommend that you use this method to resolve this problem.

Install SQL Server 2005 Service Pack 2 (SP2). For more information about how to obtain SQL Server 2005 Service Pack 2, click the following article number to view the article in the Microsoft Knowledge Base:

Method 2

Note Use this method if the following conditions are true:

• You do not want to install SQL Server 2005 SP2.
• You installed SQL Server 2005 SP1.
• You have not applied any hotfix that is based on SQL Server 2005 SP1.
• The current build of SQL Server 2005 is 2047.

Apply the hotfix that is described in the following Microsoft Knowledge Base article:

Method 3

Note Use this method if the following conditions are true:

• You do not want to install SQL Server 2005 SP2.
• You installed SQL Server 2005 SP1.
• You applied a hotfix that is based on SQL Server 2005 SP1.
• The current build of SQL Server 2005 is later than 2047.

Apply the hotfix that is described in the following Microsoft Knowledge Base article:

Method 4

Note Use this method if the following conditions are true:

• You do not want to install SQL Server 2005 SP2.
• You are running the release version of SQL Server 2005.
• You have not applied a hotfix that is based on the release version of SQL Server 2005.
• The current build of SQL Server 2005 is 1399.

Apply the hotfix that is described in the following Microsoft Knowledge Base article:

Method 5

Note Use this method if the following conditions are true:

• You do not want to install SQL Server 2005 SP2.
• You are running the release version of SQL Server 2005.
• You have applied a hotfix that is based on SQL Server 2005.
• The current build of SQL Server 2005 is later than 1399.

Apply the hotfix that is described in the following Microsoft Knowledge Base article:After you use any of these methods, you may continue to experience any of the problems that are described in the following Microsoft Knowledge Base article: In this case, apply the hotfix that is described in Microsoft Knowledge Base article 928208 if the version of the .NET Framework 2.0 is earlier than build 2.0.50727.832.

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

Some cases may occur on different operating systems and hardware platforms after the .NET Framework 2.0 hotfixes are installed. The following are descriptions of these cases together with information about how the hotfixes that are described here will ease any potential problems.

Description 1

All computers that meet the following specifications continue to operate without being affected:

• Computers that are running a Windows operating system other than Windows Vista
• Computers on which the .NET Framework 2.0 hotfixes later than build 2.0.50727.832 have not been installed

Additionally, SSIS packages are executed correctly.

Description 2

Any SSIS package that does not contain a script task or a script component is executed correctly.

Description 3

Any SSIS package that does contain a script task or a script component continues to execute correctly if you have chosen not to precompile the code.

Note This scenario is impossible on 64-bit platforms. On 64-bit platforms, there is no compiler tool to compile the code on a JIT basis.

Description 4

By default, the .NET Framework 2.0 hotfixes are installed together with the operating system on any computer that is running Windows Vista. You can resolve this issue by installing the hotfixes that are mentioned in this article.

Description 5

On 64-bit platforms, any SSIS package that contains a script task or a script component cannot be executed. You can resolve this issue by installing the hotfixes that are mentioned in this article. The only workaround is to manually recompile all scripts on a computer that has the .NET Framework 2.0 hotfixes installed. This procedure guarantees that the CIL code is both generated and consumed by the same version of the VSA. Therefore, there is no binary incompatibility.

Description 6

On all computers that are running a Windows operating system other than Windows Vista, the SSIS infrastructure tries to recompile a script that is not executed. This is true even if the .NET Framework 2.0 hotfixes are not installed. In most cases, the SSIS package continues to run without manual intervention. You may notice a very small decrease in performance because of the overhead of recompilation. The decrease is less than 1 percent. When the SSIS hotfix or SQL Server 2005 SP2 is installed, the package follows an additional step in resolving the binary incompatibility if there is a continued failure to execute the CIL code. However, the package log file and the console may contain one of the following warning messages or one of the following error messages about the failure.

Warning message 1

Warning message 2

Warning message 3

Error message 1

Error message 2

Error message 3

Overview of the interaction between SSIS and VSA

SSIS lets developers use multiple script tasks and script transforms in SSIS packages to enable custom workflow and transformation logic. The underlying technology that is used in these components is VSA. When a developer uses a script at design time, the VSA IDE starts, and the developer can program custom Visual Basic 2005 code. When the IDE is closed, the Visual Basic code is automatically precompiled to the CIL code format.

Note CIL was previously known as Microsoft intermediate language (MSIL).

Both the Visual Basic source code and the CIL code are saved in the package structure together with the other package details. When the package is executed, either during debugging or in deployed scenarios, any CIL code that is in the package is sent to the VSA engine and then executed.

Note This interaction is transparent. Typically, you would not be aware of this protocol.

You can also decide not to precompile the CIL code on a per-script basis. In this case, the Visual Basic source code is saved only in the package and is not saved in the CIL code. At run time, VSA dynamically compiles any source code that was not saved in the CIL code. Then, VSA executes the ensuing CIL code by using the method that was described earlier. However, certain compilation components of VSA have never been supported on 64-bit computers. Therefore, the automatic compilation step fails in those 64-bit deployments. The only way to work around this problem is to make sure that you always precompile scripts that are intended for 64-bit deployments.

For more information about the naming schema for Microsoft SQL Server updates, click the following article number to view the article in the Microsoft Knowledge Base: For more information about the terminology that Microsoft uses when it corrects software after the software is released, click the following article number to view the article in the Microsoft Knowledge Base: