SSIS Package fails when execute from SQL Agent (job)

• eshwararao.ch

  Old Hand

  Points: 318

  More actions

  November 28, 2011 at 9:58 pm

  #251240

  Hi,

  I have a problem where I have an SSIS package (SQL Server 2005) that won't run properly from SQL Server Agent, but it runs fine when kicked off manually from Integration Services -> Run Package or when run in debug from Visual Studio.

  The package is very simple take data from a table and load it to flat file. I created a login, credentials, proxy (used the credentials) and the proxy user has access to the shared path, i gave UNC (\\servername\test\test.txt) but still the job is failing with error "Executed as user: proxy account. The package execution failed. The step failed". i am able to execute by using UNC path using ssis directly. I have given the package file location in the job step. The account that am using to run the package from job has all permissions.

  Thanks,

  Eshwar

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  November 28, 2011 at 10:01 pm

  #1413763

  Have you given us the complete error message? It looks quite short and seems to be missing some information.

• eshwararao.ch

  Old Hand

  Points: 318

  More actions

  November 28, 2011 at 10:08 pm

  #1413765

  Thanks for the reply.

  The package is getting executed when i execute it manually either from BIDS or from the management studio Integration services. but if i call it from a job it fails, i created a proxy account(its a domain account) and gave all the required permissions and this user has all the required shared path permissions also. The issue i see in the job history is "Executed as user: proxy account. The package execution failed. The step failed".

  Regards,

  Ram.

• Koen Verbeeck

  SSC Guru

  Points: 259165

  More actions

  November 29, 2011 at 12:02 am

  #1413807

  eshwararao.ch (11/28/2011)

  ---

  Thanks for the reply.

  The package is getting executed when i execute it manually either from BIDS or from the management studio Integration services. but if i call it from a job it fails, i created a proxy account(its a domain account) and gave all the required permissions and this user has all the required shared path permissions also. The issue i see in the job history is "Executed as user: proxy account. The package execution failed. The step failed".

  Regards,

  Ram.

  That is the same information you already gave us. You are probably looking at the summary for the job. Look at the specific step that failed to see it's detailed logging.

  Also, enable logging in your package so that you can see what is going on.

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP

• kramaswamy

  SSCoach

  Points: 18181

  More actions

  November 29, 2011 at 8:14 am

  #1414113

  Try executing it like this:

  DECLARE @DTExec VARCHAR(500)

  SET @DTExec = 'dtexec /FILE "[file path]" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E'

  -- Insert statements for procedure here

  EXEC xp_cmdshell @DTExec

  Also, make sure you are not using EncryptAllWithUserKey or EncryptSensitiveWithUserKey, as those options will not work very well if you are trying to run them from an agent. Use EncryptAllWithPassword or EncryptSensitiveWithPassword instead, and add the line /DECRYPT [Password] to the dtexec command.