Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 ssis script component failure

Author  Topic 

aratib
Starting Member

3 Posts

Posted - 2012-11-07 : 06:50:35
Hi,

I am facing an issue with ScriptComponent in ssis 2005.
The details are as below.

SSIS 2005 packages have been created & they were working fine for last two years.These packages were invoked via c# asp.net page(.net 2.0).

As per projects new requirement, we were suppose to move to .net 4.0,
but we are using sql server 2005.

The c# code invoking the package is changed for .net 4.0. But the package related stuff is not changed.

Microsoft.SqlServer.ManagedDTS, Version=9.0.242.0 is used for executing package.

The changed c# code worked fine on local machine(windows 7).
The packages are executing properly locally.

When the packages & c# asp.net code moved to production machine(windows 2003), the packages are failing on script component.
The script component has vb code written in it.

The earlier code & packages are working fine there. But new code & same packages are not working.

The error is

"System.NullReferenceException: Object reference not set to an instance of an object.
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.CreateUserComponent()
"


The script component code will get executed in .net 4.0 or 2.0?
What could be the issue?


Note : Sql server 2005 SP 2 has been installed there.

Thanks!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-07 : 09:06:46
From your description it sounds like the script component should still be using the 2.0 runtime.

If you have the ability to do so, if you can invoke a package manually to see if that runs correctly. If it does not run, then something in the package itself must have changed - either the .net version or something else. If it runs, that would mean that the the dependency on 4.0 is coming from the asp.net page - but I don't have any explanation for how that depenency tunnels its way to the script component.

Has the production machine environment changed at all?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-07 : 09:37:55
As far as I know script component support .NET 4.0 only in SSIS 2012 not in earlier versions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

aratib
Starting Member

3 Posts

Posted - 2012-11-07 : 09:50:06
Thanks Sunitabeck .

Package runs from file system and through sql server 2005 as well.
Production environment contains asp.net 2.0 and asp.net 4.0 runtime. And these packages runs in asp.net 2.0.
But getting error when ssis package invoke through asp.net 4.0 .
Here getting exception,
"System.NullReferenceException: Object reference not set to an instance of an object.
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.CreateUserComponent()
"
Does ssis package compiles again script component code(which is in vb) in asp.net 4.0 runtime?
IF yes, then how can we set runtime environment to rn on 2.0?
If no, then it should not throuw any exception because its precompiled dll.

But on local machine with windows 7 OS, asp.net 4.0 runtime and same package runs successfully, but in production machine windows server 2003 it has an issue.
Go to Top of Page

aratib
Starting Member

3 Posts

Posted - 2012-11-09 : 01:26:17
Thanks.
I solved this issue.
We can use ssis package developed in sql server 2005 and asp.net 2.0, in asp.net 4.0 runtime.
We need to set precompile property of script component to "True".
When we set precompile property from "False" to "True" explicitly open VSA(on click of "Design script" Visual studio opens with script code) and save the script. This will precompile your script.

Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-09 : 06:57:54
Thanks for posting back, aratib. Good information to be aware of!
Go to Top of Page
   

- Advertisement -