September 23, 2008 at 12:56 pm
After trying many different attempts, I followed the instruction in the following URL
http://www.sql-server-performance.com/articles/dba/package_configuration_2005_p1.aspx
which describes setting up a SQL Configuration Type of connections.
I setup the necessary database and tables in the SQL server.
I then configured my SQL connector to use the SQL Configuration Type; and this worked.
I then configured my Oracle 10g connector to use the SQL Configuration Type and it did not work. I added the userName and password objects and still get the error:
"Test connection failed because of an error in initializing provider. ORA-01017; invalid username/password; logon denied.
I also edited the connection string in the SQL table for this connection and added the password=????????, but this did not help.
If I add the password on the Connection Manager screen, the test is successful. I believe that this indicates that the information in the SQL database for this connection is not being processed.
Any ideas or suggestions would be appreciated.
Thanks in advance,
-Steven Nelligan
September 24, 2008 at 6:20 am
When exactly are you experiencing this error?
When you run this in designer mode or after the package is deployed? Remember that SSIS will not save sensitive info by default when you deploy the package.
~PD
September 24, 2008 at 6:37 am
After configurating the connection, I press the test connection button and get the error.
September 25, 2008 at 12:00 am
snelliga (9/23/2008)
If I add the password on the Connection Manager screen, the test is successful.
Where exactly did you achieve success?
Can you connect with your userid and password to Ora outside of SSIS (via Toad or some other means?)
September 25, 2008 at 4:55 am
If I setup the connection thru the connection Manager and do NOT have Package Configurations enabled, the connection works. When I enable package configurations with an XML file, the connection fails when pressing the "TEST CONNECTION" button with invalid userid/password. I have added the password to the XML file.
-Steve
September 25, 2008 at 5:00 am
Are you sure the connectionstring is set up properly in the xml file?
I havent really played with xml configurations, but I use SQL server configurations extensively and normally if I have connection issues it is due to incorrect connectionstrings.
September 25, 2008 at 5:07 am
Attached is a version of the XML file I am using.
I have used two different formats for password one with = and one with :
I have seen both while searching the Web.
I have also used the Microsoft oracle OLE drive and the Native Oracle OLE Drive.
neither appear to work.
I have a SQL connection using the windows authencation, and it appears to be working,
but It would be hard to really test.
-Steve
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply