SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 ssis script component failure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

aratib
Starting Member

India
3 Posts

Posted - 11/07/2012 :  06:50:35  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/07/2012 :  09:06:46  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 11/07/2012 :  09:37:55  Show Profile  Reply with Quote
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

India
3 Posts

Posted - 11/07/2012 :  09:50:06  Show Profile  Reply with Quote
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

India
3 Posts

Posted - 11/09/2012 :  01:26:17  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/09/2012 :  06:57:54  Show Profile  Reply with Quote
Thanks for posting back, aratib. Good information to be aware of!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000