November 6, 2013 at 2:44 am
Thought I'd share my experience of setting up an ODBC connection to our source Progress database using the OpenEdge 10.2B Driver. After inheriting our data warehouse with all the connections set up by my predecessors, I had to create a new connection on my local dev machine to our ERP's test database. Making the necessary minor change to a copy of the live connection DSN, testing the DSN in the ODBC administrator was successful. Set up a new connection in SSIS 2012 and fail - Insufficient information to connect to data source. Everything seemed fine - no deviation from the successful DSN connection and then using same DSN in MS Query is again successful. But in each of those cases, a popup would re-confirm the DSN settings before successfully connecting. At this point it is worth noting that my connection did not require a password, so I didn't enter one against the username in the DSN or in SSIS 2012. And that was the problem - with a completely empty password, the connection string must not include the password attribute at all, and this fails the connection. I guess in MS Query that if the initial connection fails it will throw the connection confirmation screen from the ODBC driver where the empty password attribute is properly passed through. But in SSIS, something needs to be in the password in order to pass that through to the ODBC. Setting the password to a space and the connection is successful.
November 6, 2013 at 2:48 am
Odd. One would think a space and an empty string are not the same.
Thanks for sharing.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply