6

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.

billinkc
  • 59,250
  • 9
  • 102
  • 159
bp3849
  • 61
  • 1
  • 2
  • Any chance having the package file on a separate drive from the DTExec.exe could cause a problem? – bp3849 Feb 24 '15 at 06:30
  • 3
    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 – billinkc Feb 24 '15 at 15:08
  • Are you add owner? Like on the screen [link]http://pl.tinypic.com/view.php?pic=2hdcopg&s=8#.VOyWqS6gTEs – Svmurvj Feb 24 '15 at 15:18
  • 1
    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 – billinkc Feb 24 '15 at 17:29
  • @bp3849 have you tried another driver (OLE DB, etc.)? – Sam Apr 02 '15 at 16:18
  • owner is sa so I assume that has all permissions – bp3849 May 25 '15 at 18:00
  • Package runs as agent, Sage is setup to use u/p. All of which works if I run in VS or command line. – bp3849 May 25 '15 at 18:01
  • Have you tried setting up the connection as an ODBC DSN? This approach has worked well for me in the past – SinisterPenguin May 27 '15 at 10:34

2 Answers2

0

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".

SinisterPenguin
  • 1,610
  • 15
  • 17
0

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

Keag1
  • 141
  • 1
  • 11