It is a common situation in Integration Services (SSIS) that we may find some actions that cannot be implemented with existing control flow items; this can lead us to use the Script Task that opens a flow for implementing the action. Again, it is a common scenario that we may use existing components (Mostly .NET assemblies and COM components) that have already been built (and published). This article shows the way you can access your pre-built .NET components via the Script Task along with some important points you may want to consider.

Referencing .NET Assemblies

Referencing assemblies is done at design time. Let's take a small scenario: assume that we are in the process of writing an ETL solution and accessing an encrypted file is one of the solutions tasks. For decrypting process, we will be using an existing component that is implemented as .NET assemblies and will be done via Script Task. Note the sequence of instructions given below.

1. Drag and drop a Script Task onto your Control Flow. Open the Script Task Editor by right clicking on the task and clicking on Edit in the context menu.
2. Select Script in the left pane and click on Design Script to open a Visual Studio for Applications (VSA) environment. You may pass the encrypted file name via the ReadOnlyVaribles property.
3. Referencing is done through the Project Explorer. Open if it is not open (View -> Project Explorer). Open the Add Reference dialog box by right clicking References and clicking the Add Reference menu item.

   Note that not all available assemblies are displayed in the Add Reference dialog box. If the assembly for the decryption process is not visible, you need to manually add the assembly to this folder:

   %WINDIR%\Microsoft.NET\Framework\<latest 2.0 .NET version>

   Once added, the assembly will be available for referencing. Note that this is not a limitation of SSIS but rather VSA.

4. Select the assembly from the top pane and click Add. The selected assembly will be added to the bottom pane. Click OK to save the setting. Now you can use the assembly for writing codes for decryption.

Executing the package

Once the code is completed, you can run the package and see whether the encryption routine works properly. You may get the following error;

Could not load file or assembly ‘YourAssemblyName', Version=1.0.0.0, Culture...

if the assembly is not added to the Global Assembly Cache (GAC). It is required that the assembly exist in the GAC for access by the SSIS run-time environment. If you get the error, add the assembly to GAC. One thing to note: the assembly needs to be a signed one in order to add it to GAC.

Interactive Mode

The Runtime Error dialog box appears when SSIS runtime encounters an error in the Script Task. This dialog box appears based on the value of System::InteractiveMode variable that can be set through Project Properties -> Debugging -> InteractiveMode. The default value for this property is true. You may have already noticed that this Runtime Error dialog box does not appear when the package runs with DTExec utility. This is because DTExec does not set the InteractiveMode to true. If you are expecting to see error messages with DTExec, you must handle the error and show it with a message box. See the next section for error handling.

Error Handling

Not all components give us the error messages the way we want to see them. In many cases, we have to handle the exception and then throw our own message. I believe that capturing exceptions and handling them in our own way to be a good practice, but I know you may not agree. The usual Try/Catch blocks allow us to handle exceptions inside the Script Task, and once the error is handled, the task is no longer a failed task. You can show the error message with a dialog box, or can be written to the log. However, if you have implemented any Event Handlers, messages will not go through the Event Handlers. If it is required, make sure that FireError event is raised and TaskResult is set to Failure. The below code shows a sample code:

        Try
            'Execute the method in .NET assembly
    Catch ex As Exception
            'Raise the error again and the result to failure.
            Dts.Events.FireError(1, ex.TargetSite.ToString(), ex.Message, "", 0)
            Dts.TaskResult = Dts.Results.Failure
        End Try

64-bit assemblies

If you have installed SQL Server on a 64-bit environment, you may have noticed that Business Intelligence Development Studio runs as a 32-bit application. Not only BIDS, VSA also is a 32-bit application. The majority of us do the development on 32-bit environments. Problems can come up when you are given a 64-bit assembly for one of your SSIS packages that is supposed to be used with Script Task. You will be facing some problems with referencing your 64-bit assembly because you cannot reference 64-bit assemblies with 32-bit VSA. In this case, we need to have a 32-bit assembly for referencing. But we need make sure that 32-bit assembly's name, version and metadata are same as the 64-bit assembly. If everything is okay, we can reference the 32-bit assembly at design time and when the package runs under the 64-bit environment, the run time will pick the 64-bit assembly from the GAC if it is available.

Please note that the 64-bit scenario has not been tested by me. I had only gathered the info from some references. You may come up with a different approach to handle the situation.

Conclusion

This is a short-note about accessing .NET assemblies. Hope the information on this will be helpful on your development. Appreciate your comments on this.