We help IT Professionals succeed at work.
Get Started

SQL 2008 R2 SSIS package fail using SQL Agent

3,069 Views
Last Modified: 2012-05-11
Hi Experts,

I have a package, works fine when I use the Execute Package Utility in SSMS.
The problem arises when I make a SQL Job for it.
Before I schedule a SQL Job I test it by manually starting the job.

My problem is.
I have a 3rd party ODBC driver "EasySoft", which is failing to recognise the fact there is a DSN setup for it.
I have tried run the package using a Proxy account and the SQL Server Agent account. Both of which are sysadmins to the SQL server.
I have tried the use 32 bit runtime and the 64 bit runtime.

My DSN has been setup using C:\WINDOWS\SysWOW64\odbcad32.exe (which I think is a 32bit driver).
My ODBC Driver is not available when I use the Administrative Tools, Data Sources ODBC options. I can only see the SQL Server Drivers (which I assume is 64 Bit).
 
Why can Execute Package Ulitity see my DSN whereas the SQL job cannot?

What else can I try?

Thanks
Tony


Executed as user: SQL AGENT NT ACCOUNT. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.1600.1 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  1:40:01 p.m.  Error: 2011-05-17 13:40:09.02     Code: 0xC0047062     Source: MY DATAFLOW TASK ADO NET Source Easysoft MY DSN[1670]     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: 2011-05-17 13:40:09.02     Code: 0xC0047017     Source: MY DATAFLOW TASK SSIS.Pipeline     Description: component "ADO NET Source Easysoft MY DSN" (1670) failed validation and returned error code 0x80131937.  End Error  Error: 2011-05-17 13:40:09.02     Code: 0xC004700C     Source: MY DATAFLOW TASK SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2011-05-17 13:40:09.02     Code: 0xC0024107     Source: MY DATAFLOW TASK Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  1:40:01 p.m.  Finished: 1:40:40 p.m.  Elapsed:  38.891 seconds.  The package execution failed.  The step failed.

Open in new window

Comment
Watch Question
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 11 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE