June 30, 2008 at 1:11 pm
Looking for some ideas. I have been trying to create an SSIS data flow step that pulls data from an Oracle database and loads it into a SQL Server table. I can run this on a different SQL server and locally through Visual studio, but on this new server, I continue to receive this error. I have SA access on the other server but cannot on this one. I had the admins set the owner to SA and I can get the job to run through SQL Server Agent, but I continue to receive this Oracle Error. I can remote desktop in and connect to the Oracle Database through SQL Plus or setup an ODBC connection. I even have .NET pages I use to connect to the database through IIS on the same server, but I am having problems with the SSIS package. Does anyone have any ideas where I need to look.
Thanks
June 30, 2008 at 1:27 pm
What is the error message?
June 30, 2008 at 1:30 pm
Here is the error from the SSIS execution: 06/30/2008 13:21:05,Core OPS TRAMS Extract,Error,1,TRINIDAD-SQL\SQL1,Core OPS TRAMS Extract,Pull TRAMS Data,,Executed as user: NOAD\$sqlservice. ...n 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 1:21:05 PM Progress: 2008-06-30 13:21:06.43 Source: Data Flow Task Validating: 0% complete End Progress Progress: 2008-06-30 13:21:06.46 Source: Data Flow Task Validating: 50% complete End Progress Error: 2008-06-30 13:21:11.43 Code: 0xC0047062 Source: Data Flow Task DataReader Source [1] Description: System.Data.OracleClient.OracleException: ORA-12154: TNS:could not resolve the connect identifier specified at System.Data.OracleClient.OracleException.Check(OciErrorHandle errorHandle Boolean omitOracleConnectionName) at System.Data.OracleClient.OracleInternalConnection..ctor(OracleConnectionS... The package execution fa... The step failed.,00:00:06,0,0,,,,0
June 30, 2008 at 1:38 pm
I know your access is limited, but if you can terminal in and TNS ping the server, you should be able to terminal in and try to open/run the package through Visual Studio directly on the server. This may help you troubleshoot more actively.
The error suggests an Oracle client issue. Verify the oracle client version on your server is the same as on your workstation and test server. This type of unknown host error could be an issue with sub-nets that TNS ping is ok with - if the TNSNames file has the server name in it, try the IP address of the oracle server instead. Also verify SQL versions, MDAC versions, and service packs for the SQL Server and the Client tools (the server and client tools may not be on the same service pack).
July 1, 2008 at 8:22 am
Hmmm, I verified the software and tools are the same.
I also ran the package through Visual Studio on the server and it ran just fine.
The package merely pulls a query from the Oracle database and stores the results into a SQL-Server table.
I'm at a loss as to why the package will not run through the SQL agent when it runs through the VS 2005 debugger just fine. The owner is set to sysadmin. I have the package security set to store secure data with a password I supplied. I wonder if it is a security issue?
I continue to receive the same error.
July 1, 2008 at 8:50 am
I was not aware that the package would run through VS on the server.
Try logging into the server as the user used for the startup account for the SQL Agent service. See if you can run the package through VS that way. It could be something missing in the oracle configuration for that particular user.
July 1, 2008 at 10:40 am
FINALLY Got it to work! Found another thread entitled "Problem with running SSIS packages" that helped. Problem was my Oracle connection password would not save. It all appeared to come down to having write access to the folder the server pulls the binary from. I tried pulling it from the SQL SSIS repository and couldn't get the password to same manually. I then stored the SSIS binary in an IIS folder I have my Web Pages in and the Agent Admin tool saved my password for the connection string. I'm now running fine through SQLAgent. thanks!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply