Solved

SQL 2008 Server Agent fails job based on architecture mismatch

Posted on 2011-02-16
3
1,449 Views
Last Modified: 2012-05-11
HI,

I have an SSIS package that works fine when i manually execute it in SQL Server 2008 via the Execute Package Utility (or via the BIDS) however I've set up an SQL Server Agent to run this package nightly and it fails every night with the below messages.

Executed as user: MS\administrator. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.2531.0 for 64-bit  
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    
Started:  10:50:03 AM  Error: 2011-02-15 10:50:11.43     Code: 0xC0047062    
Source: RETRIVE VWPostPhoneData ADO NET Source 1 [71]    
Description: System.Data.Odbc.OdbcException: ERROR [IM014] [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application    
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)    
at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle)    
at System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection outerConnection, OdbcConnectionString connectionOptions)    
at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)    
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)    
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)    
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)    
at System.Data.Odbc.OdbcConnection.Open()    
at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction)    
at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager100.AcquireConnection(Object pTransaction)    
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)    
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper, Object transaction)  
End Error  Error: 2011-02-15 10:50:11.44    
Code: 0xC0047017    
Source: RETRIVE VWPostPhoneData SSIS.Pipeline    
Description: component "ADO NET Source 1" (71) failed validation and returned error code 0x80131937.  
End Error  Error: 2011-02-15 10:50:11.44    
Code: 0xC004700C    
Source: RETRIVE VWPostPhoneData SSIS.Pipeline    
Description: One or more component failed validation.  
End Error  Error: 2011-02-15 10:50:11.44    
Code: 0xC0024107    
Source: RETRIVE VWPostPhoneData      
Description: There were errors during task validation.  
End Error  DTExec: The package execution returned DTSER_FAILURE (1).  
Started:  10:50:03 AM  Finished: 10:50:11 AM  Elapsed:  7.937 seconds.  The package execution failed.  The step failed.
0
Comment
Question by:markserv
  • 2
3 Comments
 
LVL 29

Accepted Solution

by:
mass2612 earned 500 total points
ID: 34912942
Hi,

when it works correctly are you running it from another machine (such as a workstation)? It might be a problem with the DSN setup. It might be that the DSN you have setup is using the 64 bit version which is what is under the Start menu shortcut.

Try setting up another connection using : -
c:\windows\sysWOW64\odbcad32.exe

How to Create an ODBC Connection for 32-bit application on 64-bit Windows
http://protegeit.blogspot.com/2009/10/how-to-create-odbc-connection-for-32.html

Managing Data Sources
http://msdn.microsoft.com/en-us/library/ms712362%28VS.85%29.aspx
0
 

Author Comment

by:markserv
ID: 35013702
Hi,

terribly sorry for the late reply on this one.

when i run the ssis package it's being run on the same machine that the package was made.
0
 

Author Closing Comment

by:markserv
ID: 35014221
Looks like it was compiled using an ODBC from the 64-bit tool. also needed to modify the SQL Server Agent to run in 32-bit mode.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
To efficiently enable the rotation of USB drives for backups, storage pools need to be created. This way no matter which USB drive is installed, the backups will successfully write without any administrative intervention. Multiple USB devices need t…
This tutorial will walk an individual through the steps necessary to install and configure the Windows Server Backup Utility. Directly connect an external storage device such as a USB drive, or CD\DVD burner: If the device is a USB drive, ensure i…

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now