Job created on SSIS does not work on SQL SERVER AGENT

Asked

Viewed 1,051 times

1

I have a job created in SSIS (working perfectly) that does not work in SQL SERVER AGENT. The job extracts some data from an access table, via ODBC, and loads it into a sql server location table. Sql Server and the access database are on different servers, both with x86 structure. I put both the job in SQL Server and SSIS configuration to run in x86, but I still can’t run the job in Sql Server Agent.

Below the error shown:

01/14/2016 12:49:15,carrega_hlr_join_atem,Error,1,W7RJO071543,carrega_hlr_join_atem,Exec Package,,Run as user: EMBRATEL EGSANTO. Microsoft Package Execution Utility (R) SQL Server Version 11.0.5058.0 for 32-bit Copyright (C) Microsoft Corporation. All rights reserved. Home: 12:49:15 Error: 2016-01-14 13:06:34.12 Code: 0xC0014020 Origin: Package Connection Manager "TB_HLR" Description: ODBC error -1. End of Error Error: 2016-01-14 13:06:34.13 Code: 0xC0014009 Source: Package Connection Manager "TB_HLR" Description: Error when trying to establish an ODBC connection to the database server. End of Error Error: 2016-01-14 13:06:34.13 Code: 0x0000020F Origin: Task Data Flow Nefone (Access) [67] Description: Failed to call the Acquireconnection method to the TB_HLR connection manager with error code 0xC0014009. Perhaps there are error messages posted before this one with more information about why the Acquireconnection method call failed. End of Error Error: 2016-01-14 13:06:34.13 Code: 0xC0047017 Source: Task SSIS.Pipeline Data Flow Description: Nefone (Access) validation failed with error code 0x80004005. End of Error Error: 2016-01-14 13:06:34.13 Code: 0xC004700C Source: Task SSIS.Pipeline Data Flow Description: Failed to validate one or more components. End of Error Error: 2016-01-14 13:06:34.13 Code: 0xC0024107 Source: Task Data Flow Description: There were errors during the validation of the task. End of Dtexec Error: Package execution returned DTSER_FAILURE (1). Start: 12:49:15 Conclusion: 13:06:34 Elapsed time: 1038.37 seconds. Failed package execution. Step failed. ,00:17:19,0,0,,0,0,0

Would you kindly help me with this problem?

Thanks for your help!

sql sql-server ms-access odbc ssis

2 answers

1

When you run it by Visual Studio does it work correctly? If so, it is likely that it does not have the 64-bit version of access driver installed on the server. (this is from 2010, download the corresponding version of your access). https://www.microsoft.com/en-us/download/details.aspx?id=13255

Check that 64-bit drivers are installed. If you can’t check or you don’t have access to touch it, try to run the job by checking the "32-bit-Runtime" option in the "Configuration/Advanced" tab of JOB in SQL Agent.

  • But do I need the 64-bit driver even though both environments are 32-bit? I already run the job with this setting checked! Thanks for the help!

  • Dude, the job you run is on the server ? or is it on the development machine itself ?

  • They are two "deskservers"! One is with SSIS and SQL SERVER AGENT and the other is with the Access database...

0


  • André I set up an access ODBC on the machine and in SSIS I used an ODBC source box with this configuration created coupled. That’s what you meant in my stackoverflow post.com?

  • You did this as an administrator and created a system or user input?

  • I did it as Adm and created a system input.

  • Your message has: "There may be error messages posted before this one with more information about why the Acquireconnection method call failed." Take a look at Event Viewer and see if there are any errors.

  • Thanks André, I did what you said in my question on stackoverflow.com to use ADO and it worked.

  • Good, @rcockell, I’m happy.

Show 1 more comment

Browser other questions tagged sql sql-server ms-access odbc ssis

You are not signed in. Login or sign up in order to post.