SSIS package works in Visual Studio, AND Command line but not in Agent

Question

I have an SSIS package which I have developed on the server using VS. The package runs fine in Visual Studio, however when I add to a Job in SQL Server Agent the package fails. I am pulling data from SAGE Line 50 v19, so the ODBC driver is 32 bit. And I am running in a 64 bit environment. Already setup odbc as 32 bit, ensured that agent was setup to run 32 bit, but sill encountered error. I have tested running the package from command line as follows

"C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\DTExec.exe" /FILE "E:\Reporting\Projects\SSIS\SSIS2012\SSIS2012\Package1.dtsx" /DECRYPT password /CHECKPOINTING OFF  /REPORTING EWCDI

This works just fine when I run it. However when I setup an sql agent job to run as a "Operating System (CmdExec)" I get the following output.

Microsoft (R) SQL Server Execute Package Utility
Version 11.0.2100.60 for 32-bit
Copyright (C) Microsoft Corporation. All rights reserved.

Started:  13:30:44
Info: 2015-02-24 13:30:44.45
   Code: 0x40016044
   Source: Data Flow Task Data Flow Task (SSIS.Pipeline)
   Description: Attempting to upgrade "ODBC Source". The package is attempting to upgrade an extensible object.
End Info
Error: 2015-02-24 13:30:44.59
   Code: 0xC0014020
   Source: SSIS002_SageCompany5Import Connection manager "Sage005.Manager"
   Description: An ODBC error -1 has occurred.
End Error
Error: 2015-02-24 13:30:44.59
   Code: 0xC0014009
   Source: SSIS002_SageCompany5Import Connection manager "Sage005.Manager"
   Description: There was an error trying to establish an Open Database Connectivity (ODBC) connection with the database server.
End Error
Error: 2015-02-24 13:30:44.59
   Code: 0xC00291EC
   Source: Get Max(SourceID) Execute SQL Task
   Description: Failed to acquire connection "Sage005.Manager". Connection may not be configured correctly or you may not have the right permissions on this connection.
End Error
Warning: 2015-02-24 13:30:44.59
   Code: 0x80019002
   Source: SSIS002_SageCompany5Import 
   Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
End Warning
DTExec: The package execution returned DTSER_FAILURE (1).

I can't understand why this runs fine everywhere else. Been googling for hours, tried everything I can think of with no luck.

Nassi

Any chance having the package file on a separate drive from the DTExec.exe could cause a problem? – Nassi

Excellent work on your first question. You have gone through and hit the high notes for identifying what you're doing, what the problem is and what you've tried. Thank you on that – Optime

Are you add owner? Like on the screen [link]pl.tinypic.com/view.php?pic=2hdcopg&s=8#.VOyWqS6gTEs – Merengue

What I see that seems odd in the output are Attempting to upgrade "ODBC Source" and There was an error trying to establish an Open Database Connectivity (ODBC) connection with the database server When the package runs under SQL Agent, is it running as Agent or is there a Stored Credential & Proxy involved? If it's the former, how is the authentication set up for the Sage server? Is it a username and password or does it look at the current user? if credentials, where are those stored? Text file? If that, perhaps it's on the file system where agent account has no permissions – Optime

@Nassi have you tried another driver (OLE DB, etc.)? – Galumph

owner is sa so I assume that has all permissions – Nassi

Package runs as agent, Sage is setup to use u/p. All of which works if I run in VS or command line. – Nassi

Have you tried setting up the connection as an ODBC DSN? This approach has worked well for me in the past – Further

Answer 1

I am doing something similar with a 32 bit DSN & I had the check the "32-bit runtime" check box in the SQL job (under the SSIS job step - advanced tab).

Looking at the Operating System (CmdExec) option it looks like there is a "/X86" switch - maybe try that if you have to run from command line or just check the box above?

Apologies if this is what you have already done when you say the agent is "set up to run 32 bit".

Further

Answer 2

Not 100% sure if this applies to you but i had a similar problem, where my cmd app in the ssis project ran perfectly in VS but once deployed to ssisdb it would hang, and never complete turned out the cmd required a user input and with it being in the ssisdb it didnt display so you could not continue and it would error

Hope this helps a little

Sordid