Dynamic connection string not working in SSIS package 2008R2

Question

I am using SSIS package for Import data from excel sheet to SQL DB. In the configuration file I'm using following variables.

 Name                 scope     DataType   Value
----------------------------------------------------
 DBName               package   String     DB_Master
 Password             package   String     xx
 UserName             package   String     sa
 ServerName           package   String     xxx.xxx.x.xx
 SqlConnectionString  package   String     DataSource=xxx.xxx.x.xxx;UserID=sa;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Initial Catalog=DB_Master;Password=xx

Right click OLEDB ConnectionManager then click properties window below values assign

ConnectionString - Initial Catalog=DB_Master;DataSource=xxx.xxx.x.xxx;UserID=sa;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;

DelayValidation - True

Expressions     -ConnectionString - @[User::SqlConnectionString]

In SqlConnectionString variable properties window below values assign

 EvaluateAsExpression    -True
 Expression              -"DataSource=" +  @[User::ServerName] + ";UserID=" +  @[User::UserName] + ";Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Initial Catalog=" +  @[User::DBName] + ";Password=" +  @[User::Password]

After deploy the package file I change the DBName in SqlConnectionString DB_Master to Master but package execute the files in DB_Master.

and also if i give invalid ServerName like 198.152.1 but package executed successfully.

Please tell me where i made mistake. what's goes wrong...?

Answer 1

@gurunathan check the output for warnings that the configurations are not being set. When configs are not found, the design time values are used instead.

When you deploy a package with the manifest, the Config file is not automatically deployed. You would need to copy the dtsconfig file to your target server to the path that you specified in the package. I.e. C:\MySsisConfigs\configfile.dtsconfig

Alternatively, you can use an environment variable for an indirect configuration. This makes deployment to different environments easier. Here's a reference on that.

Note that if you start adding environment variables, be sure to restart related services- SSIS or sql agent, if you ultimately schedule it that way. Services cache the values of environment variables on start up.

EDIT: There were a couple of problems with the configuration: - the connection string was set to an expression based on other variables but also being set by a package configuration. - the connection string was invalid so the design time values were being used. Data Source was DataSource and the security was set to both integrated security, but also being passed a user name and password.

The tip is that if you are using a package configuration on a connection manager and the connection manager is broken when you open the package, something is not right.

Answer 2

I find solution for my issue . With help of Mark Wojciechowicz. Thank u Mark.

below changes i done.

1. Remove the Integrated security from the Connection String
2. Put space between Data Source in connection string
3. Remove the Expression for ConnectionString Variable(set EvaluateAsExpression to False)

4. Delete the following variables - ServerName,UserName,password,DBName.

   Data Source=xxx.xxx.x.xxx;User ID=xx;Provider=SQLNCLI10.1;Auto Translate=False;Initial Catalog=Test;Password=xxxxxxx

now it's working and show errors for invalid userID and password.