troubleshooting Question

SQL 2008 R2 SSIS package fail using SQL Agent

Avatar of Tony303
Tony303Flag for New Zealand asked on
Microsoft SQL ServerMicrosoft SQL Server 2008
11 Comments1 Solution3072 ViewsLast Modified:
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

ASKER CERTIFIED SOLUTION
Tony303

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 11 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros