The following is based on recent experience when I needed to call a web service to send out emails from Integration Services 2008 R2.
You probably already know that there are two components in SSIS that allow you to write custom .NET code. In the Control Flow we’ve got the Script Task, while the Data Flow offers us the Script Component.
In my case I wanted to encapsulate the code that uses the external web service into a custom .NET DLL, aka assembly. The code to send out emails is needed in several SSIS packages, so to be able to handle changes easily and to promote reuse, this really needed to get encapsulated into one library.
Furthermore, the library is configuring the binding with the web service completely through code so that no app.config file is needed. The assembly is developed using Visual Studio 2008 and needs the 3.5 .NET Framework.
Assuming that the assembly is already developed and fully tested, open up a package in the BIDS and add a Script Task to the Control Flow. Open up the Visual Studio Tools for Applications 2.0 (aka VSTA) development environment by clicking the Edit Script… button in the Script Task Editor.
Right-click the References node in the Project Explorer and select Add Reference….
In the Add Reference popup window, select the Browse tab and select your custom assembly. After clicking OK, you’re getting the following warning:
It says that your assembly, or one of its dependencies, requires a later version of the .NET Framework than the one specified in the project. Click the No button so that we can first set the target framework to the expected version.
To do that, right-click the project node in the Project Explorer and select Properties. In the Application page, you can see that Target Framework is set to .NET Framework 2.0.
Apparently by default the Script Task in Integration Services 2008 R2 still targets the 2.0 Framework. Change that setting to .NET Framework 3.5. You’ll get asked if it’s okay to close and reopen the current project, which is needed to change the Target Framework. That’s okay, so click the Yes button.
With this setting modified you can now add the reference to your custom assembly and start using it in the code.
Tip: in case that the reference to your assembly keeps disappearing every time you reopen the Script Task’s code, click the Save All button – that’s the one that looks like a bunch of blue floppies – in the toolbar after you’ve added the reference. Or hit CTRL + SHIFT + S.
Besides the scenario described above, a second reason when you may want (or better, have) to change the target framework version for your Script Task or Component is when you actually need to use functionality that doesn’t exist yet in 2.0. New functionality in .NET 3.5, such as Linq, can only be used if the target framework is switched to 3.5.
That’s it for now, have fun!
Valentino.
References
Where is my app.config for SSIS? by Darren Green
