Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

getting SSIS ADO to run with the agent Expand / Collapse
Author
Message
Posted Monday, October 14, 2013 11:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 14, 2013 10:13 AM
Points: 13, Visits: 65
I'm building packages to move data from Progress OpenEdge to MSSQL2008r2. It runs in VStudio on this server as well as the utility on Integration but not with the job scheduler. I built a test SSIS package using MS32bit ODBC so I could do a microsoft to Microsoft etl thinking it was PROGRESS---no sucess. I thought it must be a permissions thing on the scheduler but it will run an SSIS package with OLEDB drivers. Now I think it's pwd problems, did the change with ProtectionLevel:Don'tSaveSensitive but then how does the job get the pwd? Here the error message, Help been beating my head against a wall
Version 10.50.4000.0 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved.
Started: 11:15:01 AM

Error: 2013-10-14 11:15:02.42
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: 2013-10-14 11:15:02.43
Code: 0xC0047017
Source: Data Flow Task SSIS.Pipeline
Description: component "ADO NET Source" (1) failed validation and returned error code 0x80131937.
End Error

Error: 2013-10-14 11:15:02.47
Code: 0xC004700C
Source: Data Flow Task SSIS.Pipeline
Description: One or more component failed validation.
End Error

Error: 2013-10-14 11:15:02.48
Code: 0xC0024107
Source: Data Flow Task
Description: There were errors during task validation.
End Error

DTExec: The package execution returned DTSER_FAILURE (1).
Started: 11:15:01 AM Finished: 11:15:02 AM Elapsed: 1.311 seconds.
The package execution failed. The step failed.
Post #1504499
Posted Monday, October 14, 2013 3:27 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:54 PM
Points: 12,170, Visits: 9,115
If you use DontSaveSensitive, you need to store the username and pasword in a configuration.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1504560
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse