SSIS Job SQL Server

jrmendez
jrmendez used Ask the Experts™
on
Hi everyone

I need some help with a problem related to a execution of a SSIS Package in a SQL Server Job. I'm trying to connect an Oracle DB to extract data from it in order to inserted into a SQL Server table.

But I keep getting this error: (mainly the  ORA-12514)

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started:  11:41:41 AM
Error: 2009-07-17 11:41:42.94
   Code: 0xC001000E
   Source: Carga_Idiomas_Oracle
   Description: The connection "Carga_Idiomas_Oracle.dtsx" is not found. This error is thrown by Connections collection when the specific connection element is not found.
End Error
Error: 2009-07-17 11:41:43.32
   Code: 0xC0047062
   Source: Data Flow Task Lee los idiomas de oracle [1]
   Description: System.Data.OracleClient.OracleException: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

   at System.Data.OracleClient.OracleException.Check(OciErrorHandle errorHandle, Int32 rc)
   at System.Data.OracleClient.OracleInternalConnection.OpenOnLocalTransaction(String userName, String password, String serverName, Boolean integratedSecurity, Boolean unicode, Boolean omitOracleConnectionName)
   at System.Data.OracleClient.OracleInternalConnection..ctor(OracleConnectionString connectionOptions)
   at System.Data.OracleClient.OracleConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.OracleClient.OracleConnection.Open()
   at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction)
   at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConnection(Object pTransaction)
   at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)
   at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)
End Error
Error: 2009-07-17 11:41:43.32
   Code: 0xC0047017
   Source: Data Flow Task DTS.Pipeline
   Description: component "Lee los idiomas de oracle" (1) failed validation and returned error code 0x80131938.
End Error
Error: 2009-07-17 11:41:43.32
   Code: 0xC004700C
   Source: Data Flow Task DTS.Pipeline
   Description: One or more component failed validation.
End Error
Error: 2009-07-17 11:41:43.32
   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:41:41 AM
Finished: 11:41:43 AM
Elapsed:  1.969 seconds


I hope someone can help me here please!!!

thanks in advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
did you specify the oracle connection password in a configuration file? SSIS packages do not store the connection password, instead you should suply it in a configuration file. check taht is correct.

Author

Commented:
Yes I did specify the password in a config file XML.
Then I add the configuration file as shown in the image I attached.

Is there something wrong with that?
ssis-config.JPG
Commented:
I found what the error was.

i had the wrong listener for oracle DB.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial