Integration Services 2008

You are currently browsing articles tagged Integration Services 2008.

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….

Adding a reference to the Script Task

In the Add Reference popup window, select the Browse tab and select your custom assembly.  After clicking OK, you’re getting the following warning:

Warning when adding a custom assembly to a Script Task in Integration Services

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.

By default, the Target Framework for the Script Task in SSIS 2008 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.

Target Framework Change: requires a close/reopen of the current project

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!



Where is my app.config for SSIS? by Darren Green


Tags: ,

Consider the following scenario. You’ve been developing some SSIS packages, nicely making sure that all errors and warnings are gone from the Error List window. So the next step is to deploy the packages to the server and schedule them. After some days you decide to have a look at the sysssislog table and discover that it’s filled with OnWarning records!

Well, this is an extreme example of course. Careful developers would notice the warnings in either the Output window or the Progress tab while testing the package in the BIDS.

All these warnings are indicating that some columns in your data flows have become obsolete.  Here’s an example:

The output column “BKCustomerID” (13529) on output “OLE DB Source Output” (12088) and component “OLE_SRC MySource” (12077) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.

The problem with these warnings is not only that they slow down the transformation process but they obfuscate the SSIS log with thousands of records, making it difficult for you when you need to investigate an issue. And this is something that you really don’t want, especially when the business people are calling you to ask why the data is not up-to-date on the production servers.

If you ever wished that you’d see these warnings whilst developing the package instead of at runtime only, now’s your chance to make a change. Or better, to ask Microsoft to implement the change.

Follow this link to cast your vote on Connect!

Thank you Jamie for pointing this out.

Have fun!



Tags: , ,

Ever since I upgraded to SQL Server 2008 Service Pack 1 I noticed that the Management Studio was reporting incorrect version numbers when connected to Integration or Reporting Services.  This incorrect version number is located to the right of the server instance in the Object Explorer.

As usual, a picture says so much more than … :

Object Explorer showing wrong version numbers

As I have posted earlier, 10.0.2531 is the version number for SP1, while 10.0.1600 is the original RTM version number.

I never really spent time looking for an answer to this.  It was obviously a bug but I could live with it and someone else would probably already have filed it as being a bug.  So recently I came across a post by Phil Brammer that mentioned this issue.  This post got a comment from Matt Masson, a developer on the SSIS team.  Have a look at the comment but in short: the version numbers that are being shown in the Object Explorer are actually the version numbers of the service’s .exe file!  And SSMS is now showing the wrong number because these files didn’t get an update in SP1.

After a little search I found the bug report on Microsoft Connect, reported on March 11, 2009, by Dan English.  Its status is Fixed but it seems that it isn’t.  At least, looking at the comments, CU5 (Cumulative Update) for SQL Server 2008 SP1 is still showing the problem.  So I guess you could go over to the Connect page and click on that Yes button if you’re interested in seeing this fixed.  After all, it could be quite misleading to novice DB guys and gals…

On this same subject, there’s another interesting post by Adam W. Saxton, a member of the Microsoft SQL Server Escalation Services Team.  In this post he takes a closer look at the SQL Server 2008 Reporting Services version number after having installed CU2.

Conclusion: if you need to find out what version your server is running, do not rely on the version numbers that you see in the Object Explorer.  As Adam explained, one way is to look at the version numbers of the files that were included in the upgrade.  But that may a bit of an overkill.  My favorite way, assuming that all components of the SQL Server installation have been upgraded to the same version, is to use the following query:



On my machine that comes back with the following result:

Microsoft SQL Server 2008 (SP1) – 10.0.2531.0 (Intel X86)   Mar 29 2009 10:27:29   Copyright (c) 1988-2008 Microsoft Corporation  Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

And remember, have fun!


Tags: , , , , , , , ,

© 2008-2019 BI: Beer Intelligence? All Rights Reserved