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

Need help setting up SSIS in Visual Studio 2008 to Oracle 11g Expand / Collapse
Author
Message
Posted Thursday, February 27, 2014 1:14 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, December 15, 2014 11:29 AM
Points: 55, Visits: 297
Anybody have good references that can guide me through this process? I'm trying to set up a connection from SSIS VS 2008 selecting ADO.NET/.Net Provider/OracleClient Data Provider and putting in the server name and username and pw and when I click on "Test Connection" I get this message. "ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA"?

Here's a little context of my set up. My machine is a 64bit and the server that has the Oracle DB is also 64bit. I'm running Win 7 Enterprise. The Oracle server is Win 2008 r2 standard.
Post #1546098
Posted Friday, February 28, 2014 12:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 12:52 PM
Points: 13,636, Visits: 11,509
Did you install the Oracle Client and the ODAC components?
Did you configure TNSNAMES.ORA?




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 #1546214
Posted Friday, February 28, 2014 10:06 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, December 15, 2014 11:29 AM
Points: 55, Visits: 297
I believe so not 100% sure. How can I verify I did? I can tnsping the Oracle server fine and I have sqldeveloper connecting to the Oracle server from my machine fine. TNSNAMES.ORA on the Oracle server is configured.
Post #1546461
Posted Friday, February 28, 2014 1:42 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, December 15, 2014 11:29 AM
Points: 55, Visits: 297
I got it to work, but now when I try and query the table in Oracle it says the table does not exist? But, I see the table on SQLDeveloper. Anybody know why?
Post #1546565
Posted Monday, March 3, 2014 9:57 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 12:52 PM
Points: 13,636, Visits: 11,509
Maybe you need to prefix the schema to the table?



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 #1547013
Posted Monday, March 3, 2014 1:01 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, December 15, 2014 11:29 AM
Points: 55, Visits: 297
Found out it was a quoted identifier issue. Needed to write the table with double quotes around it. Interesting thing though is I also recreated the package with a different table name and it worked just fine.

select * from "test2".
Post #1547071
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse