Connection manager on Execute SQL task

Question

Connection manager on Execute SQL task

DotNetXenon

Old Hand

Points: 323
More actions
July 20, 2010 at 8:05 am

#224996

I have execute sql task and script tasks in my SSIS package which is deployed in Dev and Test environments.
Using package configuration, script tasks get the different configuration in Dev and Test.
I am not sure how to do this for Execute SQL task. I have defined the connection as SQLConn in the Connection manager and use it in the Execute SQL task Connection. If I have to deploy on test environment, manually I have to go and change the connection manager.
I am sure there is a way to automize this. Your help is appreciated.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

PaVeRa22 SSCommitted Points: 1965 More actions · Answer 1

Define Database/username/password variables in configuration files ( configuration files are same with diff values for both environments), and build an expression for connectionString property of a SQL connection. thats it...

Cheers

DotNetXenon Old Hand Points: 323 More actions · Answer 2

Thanks for your reply, Its a great help. I already had the package configuration in place. I have a connection manager 'Staging Connection' defined and was changing the server name there.

Now I have specified @ConnString (Variable which stores the value from the config table) in the Expressions.

When I execute the sql task, this is the error I get "the connection defined in the @ConnString variable is not found. This error is thrown by Connections collection when the specific connection element is not found"

Please help

PaVeRa22 SSCommitted Points: 1965 More actions · Answer 3

if you see the connectionString property of the database connection, you will see some thing like this...

Data Source=MyServer;Initial Catalog=myDB;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;....

Take that string and replace server and database ( if u have username/password, include that as well)

"Data Source="+@[User::varMyServer]+";Initial Catalog="+@[User::varmyDB]+";Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;...."

use this expression builder for ConnectionString property under Expressions by clicking on ..., (not the direct property), it shud be dynamic

or if you are building into a string then assign the same under EXPRESSIONS for same connectinString property.