Brandon Lilly


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

I have puttered with this for a while now and can't figure out what the problem is. Any ideas




Re: Different behavior between DTExecUI and Scheduled Job

DarrenSQLIS


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







Re: Different behavior between DTExecUI and Scheduled Job

Brandon Lilly

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






Re: Different behavior between DTExecUI and Scheduled Job

Feng Guo

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.






Re: Different behavior between DTExecUI and Scheduled Job

MatthewRoche

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.

Excellent advice!

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.

Hopefully this will help narrow down what is the significant difference in your environment.






Re: Different behavior between DTExecUI and Scheduled Job

Brandon Lilly

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 Sad





Re: Different behavior between DTExecUI and Scheduled Job

Brandon Lilly

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





Re: Different behavior between DTExecUI and Scheduled Job

jwelch

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.






Re: Different behavior between DTExecUI and Scheduled Job

MatthewRoche

I'm not sure what it tells you, but it tells me that you should run it as a CmdExec job scheduled using a proxy account.

Honestly, the thing that I take away from these results is that there is something different with using the SSIS job step type in SQL Server Agent. Sadly, I do not know the root difference here. I always use DTEXEC for my scheduled jobs and I never have problems.






Re: Different behavior between DTExecUI and Scheduled Job

Brandon Lilly

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 Stick out tongue

Thanks

Brandon





Re: Different behavior between DTExecUI and Scheduled Job

Brandon Lilly

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