SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Connection string being passed into DTEXEC is not used during execution of package


Connection string being passed into DTEXEC is not used during execution of package

Author
Message
gvaldridge
gvaldridge
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 39
(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).
gvaldridge
gvaldridge
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 39
Any help would be appreciated. Does anyone have any idea on why this behavior is occurring?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search