Home > Sample chapters

New SSIS 2012 Designer Features

Scripting

Visual Studio Tools for Applications

SSIS uses Visual Studio Tools for Applications to provide support for its .NET scripting functionality. Visual Studio Tools for Applications provides an integrated development environment (IDE), allowing users to embed VB.NET and C# code within their SSIS packages. SQL Server 2012 contains a new version of Visual Studio Tools for Applications (3.0), which is based on the Visual Studio 2010 shell, and fully supports .NET 4.

In SQL Server 2008, Visual Studio Tools for Applications was used both at design time (when the script is created) and at runtime (when the package is run). A number of changes were made in SQL Server 2012 that allow SSIS to fully embed the script and execute the .NET assembly inline without the need to spin up the Visual Studio Tools for Applications runtime. This improvement leads to some minor performance benefits, and also allows the product to support a much requested feature: Script component debugging.

Script Component Debugging

The Script task has always supported debugging, but users were limited to using message boxes and log messages to debug their Script components. Script component debugging is now supported in SQL Server 2012, allowing you to set breakpoints and walk through your code within the Visual Studio Tools for Applications designer. Figure 4-31 shows the debugging of a Script component source.

Figure 4-31

Figure 4-31 Debugging a Script component.

To enable debugging, your project must be set to run in 32-bit mode within the Visual Studio designer. This is done by opening the properties for your SSIS project and setting the Run64BitRuntime property on the Configuration Properties | Debugging tab to False (Figure 4-32). Because Visual Studio is a 32-bit application, debugging doesn’t work when the package runs in a 64-bit mode. This same limitation exists for debugging the Script task as well.

Figure 4-32

Figure 4-32 Enable debugging by setting the project’s Run64BitRuntime to False.

.NET 4 Framework Support

New Script tasks and Script components will target .NET Framework 4 by default. The target framework can be changed in the script project’s properties dialog box from within the Visual Studio Tools for Applications designer (Figure 4-33).

Figure 4-33

Figure 4-33 Enable debugging by setting the project’s Run64BitRuntime to False.