Package works in BIDS, but not in SQL Server 2005

Most of my packages that I've created in BIDS will NOT run in SQL Server 2005. The simplest one that I have fails during a script task that calls external managed code. I've done all the steps outlined in "Referencing Other Assemblies...", but I'm still getting "Object reference not set to an instance of an object." Here's a sample of a script that's having a problem. The line in green is the one that seems to be cause of the error. This is extremely frustrating. This code will even run from a command line console without error. Why is it so difficult to deploy one of these projects with managed code?

Code Snippet

Public Sub Main()
Dim variable1 As String = DirectCast(Dts.Variables("packagevariable1").Value, String)
Dim variable2 As String = DirectCast(Dts.Variables("packagevariable2").Value, String)
Dim variable3 As Integer = DirectCast(Dts.Variables("packagevariable3").Value, Integer)
Dim variable4 As String = DirectCast(Dts.Variables("packagevariable4").Value, String)
Dim filePath As String = DirectCast(Dts.Variables("filePath").Value, String)
Dim variable5 As String = DirectCast(Dts.Variables("packagevariable5").Value, String)
Dim results As Boolean
Dim fileGenerator As IProviderInterface
Dim intFactory As integrationServiceFactory = New ProviderIntegrationServiceFactory()

fileGenerator = intFactory.GetProviderEnrollmentGenerator(variable2, variable5)
results = fileGenerator.GenerateFile(variable3, variable1, filePath, variable2)

If results Then
Dts.TaskResult = Dts.Results.Success
Else
Dts.TaskResult = Dts.Results.Failure
End If

End Sub

Can you be more specific what you mean by "run in SQL Server"? How are these packages being executed?

What other types of activities are failing in your packages? Is it always related to external code?

My thinking based on what you've given so far is that you're trying to load and execute packages from inside a CLR stored procedure. When you do that, you're using SQL's hosted CLR and are subject to SQL's security constraints, such as the Host Protection Attributes.

SQL CLR will deny execution of framework code that has these attributes.
ExternalProcessMgmt ExternalThreading MayLeakOnAbort SecurityInfrastructure SelfAffectingProcessMgmnt SelfAffectingThreading SharedState Synchronization UI |||Primarily, I'm trying to execute this in a SQL job. However, if I execute it from Integration Services, the result is the same. My errors seem to be related to the external code, but I have 15 packages that all use some external code. Other activities that are failing also include decrypting files, but again, these are run in scripts that use external code.
|||

Dan Wheeler wrote:

Primarily, I'm trying to execute this in a SQL job. However, if I execute it from Integration Services, the result is the same. My errors seem to be related to the external code, but I have 15 packages that all use some external code.

"if I execute it from Integration Services, the result is the same". I don't know if that means BIDS or something else, and if "the same" means success or failure. I'm mostly going off your subject that the package works in BIDS, but not from a SQL Agent job.

The difference between executing it from BIDS and from a job is usually just the security context. Under BIDS, it inherits your permissions and under SQL Agent, it is whatever account the agent runs under, which is rarely the same. Anything going on in that code that might be affected by those different permissions? Accessing remote or secured drives or files, perhaps?

There is also another affect of the different user context that I don't see any evidence of here, but gets people all the time. That is that by default, SSIS encrypts sensitive data using a user key. When the package is executed by another user (i.e. SQL Agent), this sensitive data (passwords, mostly) are not available. See threads and documentation for "ProtectionLevel" for more information if you think that might be a factor.

|||

JayH wrote:

"if I execute it from Integration Services, the result is the same". I don't know if that means BIDS or something else, and if "the same" means success or failure. I'm mostly going off your subject that the package works in BIDS, but not from a SQL Agent job."

It means that they both fail to execute the package.

The only thing remote in this situation are the databases. This package is not failing with database connectivity issues. My security context is a limited user. What permissions would SQL Agent need?

The packages are password encrypted. I provide the password when creating the SQL job. When I execute the package in Integration Services, I provide the password. Therefore, I don't think it is a protection level issue.
|||

Dan Wheeler wrote:

JayH wrote:

"if I execute it from Integration Services, the result is the same". I don't know if that means BIDS or something else, and if "the same" means success or failure. I'm mostly going off your subject that the package works in BIDS, but not from a SQL Agent job."

It means that they both fail to execute the package.

The only thing remote in this situation are the databases. This package is not failing with database connectivity issues. My security context is a limited user. What permissions would SQL Agent need?

The packages are password encrypted. I provide the password when creating the SQL job. When I execute the package in Integration Services, I provide the password. Therefore, I don't think it is a protection level issue.

I'm still confused about whether the packages run in BIDS or not. If you can call your external code from a simple test harness, then it should work from BIDS.

The error indicates that either GetProviderEnrollmentGenerator is returning Nothing, or that the error is being generated from inside GenerateFile. Is it possible that the variables you're passing into either one of those are not what you think they are? Have you put MessageBoxes in the script to display those values? Being totally agnostic about your code, I'd suggest writing those variables out to a file or something where you can get them verbatim and then calling your external code from a test harness with those values.

|||As the subject states, these packages all execute without error in BIDS. In the first post, I note that I can run this exact code from a console app. I've already created a test harness for this purpose. Since I'm trying to execute this in Integration Services, will the MsgBox's still show up?|||

Dan Wheeler wrote:

As the subject states, these packages all execute without error in BIDS.
Quote "Dan Wheeler"
This code will even run from a command line console without error.

(as you can see, I've already created a test harness for this purpose) Since I'm trying to execute this in Integration Services, will the MsgBox's still show up?

Sorry, I think I'm confused about your use of "Integration Services". I couldn't tell if you meant BIDS or not. I also don't recall seeing that about your console app in the original post.

No, MessageBoxes will not show up if you execute from dtexec (i.e. the agent job). I was thinking about BIDS at that point.
|||Anyone else have anything to offer? I've created a deployment package and still no success.
|||

What if you try running a batch file from the agent job?

My experience mirrors Jay's on this: I think the error is security related. Does the the account that SQL Server or the agent is running under have access to all the files and folders used - not just by the package, but also by the .NET component?

sql