Different behavior between DTExecUI and Scheduled Job

I have a very simple SSIS package that reads an environment variable, assigns it to a variable that is sent in an email, and also logs it to a text file. If I execute the package on the server via DTExecUI, it works fine as expected. However, if I schedule it as part of a job, I get the following error:

Code: 0x00000002 Source: Script Task Description: The script threw an exception: Object reference not set to an instance of an object. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:08:00 AM Finished: 9:08:01 AM Elapsed: 0.859 seconds. The package execution failed. The step failed.

The script is as follows:

Imports System

Imports System.Data

Imports System.IO

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()

Dim Path As String = "C:\TestEnv.txt"

Dim Env As String = Environment.GetEnvironmentVariable("SSISConfig").ToString()

Dim Sw As StreamWriter

Dts.Variables("SSISConfig").Value = Env

If File.Exists(Path) = False Then

Sw = File.CreateText(Path)

Else

Sw = File.AppendText(Path)

End If

Try

Sw.WriteLine(Env)

Finally

Sw.Close()

End Try

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

Obviously there are differences, service accounts and that type of stuff. Read some odl posts on scheduled packages for more info, I'll skip to what I think the issue is.

I assume you have created the environment variable SSISConfig on the server, and probably as a a system variable. I'll bet the code chokes because the variable does not exist in the context of the SQL Server Agent service. Environment variables are in effect cached at the start of a process. The process here is SQL Agent, so I think you have not restarted the service/process since you added the variable DTExec works because you start the process fresh each time. So to fix, try restarting the SQL Agent service.

Any help

I will do more searching as you suggest.

I have done the following prior to my orginal post:

- Restarted Agent

- Restarted SQL Server

- Rebooted Server

None of these have done a lick of good for me and still get the error.

Thanks!

Brandon

Feng Guo wrote:

Try to create a SQL Server agent proxy. Then grant the user login which will execute the package to the proxy. After that, try to the execute the package again.

I should have mentioned that I already have. SQL Agent is running as the LocalSystem account and doesn't have access to network resources, so I created credentials for my domain account and then created an SSIS proxy, which the schedule job is using. I am an administrator on machine that Agent is running on.

Any other ideas

MatthewRoche wrote:

Also, to eliminate the tool variable from your troubleshooting equation, execute the package using DTEXEC in multiple contexts. First use DTEXECUI to build the command line syntax for DTEXEC and then: Execute the package manually using DTEXEC from the command line Execute the package from a SQL Server Agent Job (CmdExec job, NOT SSIS package job). Execute the package from a SQL Server Agent Job (CmdExec job, NOT SSIS package job) with the SQL Server Agent service configured to run as your user account, and then restarted.

Ok, the results are in:

1. DTExec command-line: Success!
2. CmdExec Job using Agent account: Will fail because without the proxy, the job cannot load the package, because it is stored on a network resource
3. CmdExec Job With Proxy Manual: Success!
4. CmdExec Job With Proxy Scheduled: Success!

So what does this tell me, exactly Thanks!

Brandon

jwelch wrote:

Did you create the environment variable as a user env. variable or a system env. variable If user, try switching it to a system variable.

That was exactly it. Was using a user environment variable and not a system variable. Makes perfect sense now that you point it out. I guess I never really understood what the difference was between them. Now I do

Thanks

Brandon

DarrenSQLIS wrote:

I assume you have created the environment variable SSISConfig on the server, and probably as a a system variable. Any help

Oh, the assumptions! Alas, I was NOT using a system variable, and when I read this initially, I completely overlooked the operative word: SYSTEM. Thanks for your help, Darren!

Brandon