none
Error when SSIS package is exceuted as scheduled job in SQL server.

    Question

  • I am receiving the below error, when the SSIS package is executed through the scheduled SQL server Job. 

    OnError,DEVDSQL03,PDC\devsqlsrvc,Data Flow Task,{AF6398C5-232F-4D33-8FD6-DE1479E17449},{8F885F81-1190-4CE0-BA97-4146EF420B03},3/1/2014 12:00:01 AM,3/1/2014 12:00:01 AM,-1073450910,0x,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)
    OnError,DEVDSQL03,PDC\devsqlsrvc,Data Flow Task,{AF6398C5-232F-4D33-8FD6-DE1479E17449},{8F885F81-1190-4CE0-BA97-4146EF420B03},3/1/2014 12:00:01 AM,3/1/2014 12:00:01 AM,-1073450985,0x,component "ADO NET Source" (1) failed validation and returned error code 0x80131937.

    I am using ODBC to connect AS400 DB. If i run the job manually, then it is executing without any error. 

    Kindly let me know, what I am missing.

    Sunday, March 02, 2014 6:44 AM

Answers

  • Hi Arul,

    Since you can run the job manually, the issue may occur because that the SQL Server Service Account or proxy under which the job runs doesn’t have access to the DSN. Is it a System DSN or a User DSN? If it is a User DSN, it may not be accessible when the job runs based on a schedule. So, please make sure it is a System DSN which can be accessed by all the users on the server.

    Regards,


    Mike Yin
    TechNet Community Support

    Monday, March 10, 2014 9:48 AM

All replies