-2

I run my package in the server, it worked fine but when I used to run its in SQL Agent job, it failed to execute.

Microsoft (R) SQL Server Execute Package Utility Version 10.50.4000.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 2:05:54 PM Error: 2014-02-05 14:06:00.54 Code: 0xC0047062 Source: Data Flow Task ADO NET Source [1] Description: System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified 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: 2014-02-05 14:06:00.55 Code: 0xC0047017 Source: Data Flow Task SSIS.Pipeline Description: component "ADO NET Source" (1) failed validation and returned error code 0x80131937. End Error Error: 2014-02-05 14:06:00.55 Code: 0xC004700C Source: Data Flow Task SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2014-02-05 14:06:00.55 Code: 0xC0024107 Source: Data Flow Task Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 2:05:54 PM Finished: 2:06:00 PM Elapsed: 5.625 seconds. The package execution failed. The step failed.

user3266252
  • 1
  • 1
  • 1

1 Answers1

0

This sort of thing is commonly down to 32/64 bit issues. You may be developing in BIDS which recognises the 32bit ODBC driver and then you are deploying to a 64bit version of SQL Server.

You can try enabling the 32 bit runtime for your Agent job step or making sure you have the correct versions of the appropriate drivers installed and configured.

Try this: http://microsoft-ssis.blogspot.co.uk/2012/09/swtich-package-from-64bit-to-32bit.html

Mardoz
  • 1,617
  • 1
  • 13
  • 26