Web Reference Breaks SSIS Package

Question

I am working on a SSIS Package using SQL Server 2012 and Visual Studio 2010 Shell.

I need to use a web method in a script task I have and I was able to run this script on windows 7 without any problems. This web service requires a certificate which I have installed in my personal computer store.

However when I moved this project into Windows Server 2008 R2, the package breaks for apparently no reason. I have no compilation errors on the Error List and if build the script task it will succeed, however when I finish editing the script a message box will appear with this error message:

"Scripts contained in the package have compilation errors. Do you want to save changes?"

If I remove the web reference from the script task, this message wont appear.

The package won't run because of this. I checked for error details on the Event Viewer, but it only shows "Package Failed" and no further details appear about this issue. Could this be related to a certificate issue? Is there an error log or way to know more details about what is causing the package to fail?

Thank you.

Answer 1

It isn't just the security issue of certificates required by the web service itself that you have to worry about.

The SSRS web server will not allow loading assemblies that are marked as "unsafe"--which is pretty much anything that does I/O. I once added a reference to a .Net Forms assembly to an SSRS report in order to use its rich text functionality. It worked beautifully in Visual Studio, but once deployed to the SSRS web server, it would never run. I am certain it could be made to function, but due to other priorities I actually gave up the job as everything I tried didn't work.

Searching online for adding custom assemblies yields some useful information for getting custom assemblies working. Here are some of the crucial steps:

• Sign the assembly with a strong name, required because it is deployed to the GAC.
• Use the assembly-level attribute [assembly:AllowPartiallyTrustedCallers] in the assembly so SSRS can use it.

A couple of likely resources are here and here.