SSIS Dynamic Connection in Execute Sql Task

http://www.sqlis.com/post/The-Execute-SQL-Task.aspx

This is a good link but does not show how to use a dynamic connection.

I can see how to map the servername and username in the Expressions of a Connection Manager object, but what I can't see is how to pass the password?  Is there an example of this somewhere?

To clearly state the question....

How do you set dynamic connection (userid, database, pwd) within an Execute Sql Task from an external table? I can set expressions within the connection manager object to user variables for the servername and the username but not the password.

Try the article I made here:
http://biresort.net/blogs/pedrocgd/archive/2009/05/13/stepbystep-ssis-using-package-configurations.aspx

Use package configurations to dinamically send values into your package
Helped?
regards,
Pedro

You can use Package Configurations or use an SQL task to get and store some values you have in the database.

To set the a connection dinamically you can use expressions in case of using SQL task. In case of using package configurations is automatique.

Helped?

Nope... didn't really help.

Again, how do I assign the user, password, database variables to a connection object used within an Execute SQL Task. This seems like it should be a simple question.

Pedro, the link you supplied does not have a link to the example anywhere that I can find.

in the link you an Attachment: SSIS_EX_01_PCKCFG.zip with the paper I made..
but dont worry I'm doing a package example for you right now... just a minute

Oh I see it. Thanks...
 
I need a simple example..I'm very new.

Also...it looks like your example is getting connection from xml file. I am supplying via a database table. I want to get a set of rows containing connection info and loop thru these changing the connection within each iteration. I do not want to use a configuration xml file.

ok.. just a moment

You have 2 databases?
If you trying to connect to one database you should know where it is and which user goes there...

I assume you have one database to store user and passwords for other dabatas, correct!?

Take a look at the attached image...

My connectionstring (for Oracle) does not contain a password parameter.

Data Source = xxxx; user id = xxxx; provider= MSDORA.1; Persist Security Info = True;

That is my question...how do you supply the password in this connectionstring?

Can I just add a PWD= ? It does not appear in the generic connection string for oracle. I need to pass the password along with all other connection info. Is there a way to test the connection parameters without running?

I tried running it and get error in connectionstring.

Take a look to several options you have here:
http://www.connectionstrings.com/oracle

and customize it as you need.
regards,
Pedro

getting error from Execute Source SQL Task now.

Failed to lock variable "select...." for read access...the variable cannot be found.

attach the package here is better!
regards,
*Pedro

ok, I think it is working but can you tell me how I can see the results? I set the ResultSetType to singlerow but this generates the error:

There is an invalid number of result bindings returned for the ResultSetType.

I'm running a very simple sql...select 'test' from dual; which should return 'test'.

Ok, set the resultset to a variable but now getting type error

I cast the numeric result to double but still doesn't like the type.