configure DATA LINK - Export SQL Server Data to Oracle using SSIS

Hi experts, I am reading about this
http://www.mssqltips.com/sqlservertip/2011/export-sql-server-data-to-oracle-using-ssis/

but I can not connect
what username and password should be?

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Enrique-PC)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )
user.png
dataLINKproperties.png
enrique_aeoAsked:
Who is Participating?
 
tigin44Connect With a Mentor Commented:
Hi,
The important point in your case is the oracle drivers used while connecting to oracle server. Which drivers are you using? Are you trying to connect via direct connection like using oracle client ODAC drivers? If your machine running 64 bit, you should also install both 32 and 64 bit driver together. The SSIS packages do run 32 bit at design time but runs 64 bit at run time. Once you discover your drivers then you can construct your connection string.

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Enrique-PC)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

is a TNS record whith the alias XE defining the connection parameters. This parameters are not enough to establish a connection,  you should also provide a user name and password.
There are many ways to supply these parameters. While constructing the connection string, by using expressions; by using SSIS Package configurations; by providing at run time via user parameters...

If you clarify the connection operation we may provide more help...
0
 
Eugene ZConnect With a Mentor Commented:
what the error did you get when try system?
what is login working when you access to oracle from sqlplus or sqldev apps?
use what you use to access oracle (you should now uid\pwd) - or ask DBA
0
 
Rainer JeschorConnect With a Mentor Commented:
Hi,
if you can ensure that the username and pwd are good - I have had once the same issue.
This was related to an older Oracle Client installation (on the SQL server) and a newer version on the Oracle system where some enhanced security configurations have been enabled - therefore no one with the older client dlls could logon.
Can you check the Oracle client / data access components version on the SSIS system?

Thanks and HTH
Rainer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.