April 30, 2010 at 1:31 pm
(I apologize that this is posted in a different (wrong) section of the forums, but I can't figure out how to move/delete it.)
I developed a test package which runs fine within BIDS. I deployed it to SQL Server and created a configuration file for it. ProtectionLevel is set to DontSaveSensitive. I set the configuration file to expose the Connection String for the one Connection Manager used in the package. Ideally, I would like to NOT have to keep the password inside the Configuration File, but pass it in during execution.
Once I run DTEXECUI, I provide all the server info and the configuration file that I created. I then go to the Connection Managers tab. I check the box for the one Connection Manager in the package. Before I do anything, the Connection String looks like this:
"Data Source=SERVER;User ID=USER;Provider=MSDAORA.1;Persist Security Info=True;"
Once I add the password, the Connection String then looks like this:
"Data Source=SERVER;User ID=USER;Password=pwd;Provider=MSDAORA.1;Persist Security Info=True;"
From what I've read, the connection string is formatted correctly after my edit.
However, when I hit the Execute button, I get "Error: Failed to acquire connection SERVER.Connection_name Connection may not be configured correctly or you may not have the right permissions on this connection".
Below that error I get another error which eventually ends with:
"ORA-01017 Invalid username/password;Logon denied"
I know for a fact that the username and password work. When I put the Password=pwd; into the Connection String inside of the Configuration file itself, the package executes without errors. Just for laughs, I entered two different passwords into both the DTEXEC connection string and the one in the configuration file. The package always uses the connection string in the configuration file. I even set DelayValidation to True in both the Connection Manager and the only task in the package, just to see if that would change which Connection string the package would use. It did not change a thing.
I'm sure the problem is my lack of knowledge in the SSIS area. I know the learning curve is pretty steep for SSIS and being a former DTS user doesn't seem to make things any easier. Surely I'm missing something here.
However, if this is working as designed, I guess I'm not sure what the point is in allowing the user to manually enter a Connection String within DTEXEC(UI).
May 3, 2010 at 3:10 pm
Any help would be appreciated. Does anyone have any idea on why this behavior is occurring?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply