0

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...?

Gurunathan
  • 97
  • 4
  • 19
  • I don't know what the problem is but you can simplify this by _just_ assigning the connection string property. It already contains all of those other values. You don't need that string concatenation expression, just assign connection string directly from the config. Then change the DB just in that config. – Nick.Mc Dec 12 '14 at 12:31
  • 1
    How are you changing the dbName in the connection string? Are you updating the connectionstring or the variable? How are you changing the value in the variable? With a configuration? Using /set? – Mark Wojciechowicz Dec 12 '14 at 12:43
  • @MarkWojciechowicz I change the dbname in connection string by using DBName variable.I don't know how to updating the connection string.I change the value of variable by enter values in connection string. – Gurunathan Dec 17 '14 at 05:26
  • @gurunathan if you change the connection string directly, it will be overwritten by the variable values. When you execute the deployed package, how are you changing the variable value at run time? – Mark Wojciechowicz Dec 17 '14 at 11:40
  • @MarkWojciechowicz I'm new to ssis package creation. so i tell what i did. Step:1 - After build the project it's create the three files in bin\deployement folder.step:2 - open DTSCONFIG file change the connection string "Initial Catalog=TestMaster" to Test. step:3 - Deploy manifest file to particular path. step:4- After deploy it's create two files in particular path then i open Integration Service package file then click Execute button. – Gurunathan Dec 17 '14 at 11:52

2 Answers2

0

@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.

Mark Wojciechowicz
  • 4,287
  • 1
  • 17
  • 25
  • I'm using variables like below link. https://anothersqlgeek.wordpress.com/2013/03/28/ssis-dynamic-connections-part-1/comment-page-1/#comment-48 but it's not work for me. – Gurunathan Dec 22 '14 at 11:29
  • Do you have warnings when you run the package? If so, are they related to configurations? Where is your configuration file on the target server? – Mark Wojciechowicz Dec 22 '14 at 12:33
  • No warnings during run. Configuration(dtsconfig) file present in bin\Deployment after build the project. Default i create config file inside project folder. – Gurunathan Dec 23 '14 at 05:02
  • The dtsCONFIG file must be on the target server. In the path that you specified in the connection manager. Have you moved it from the bin folder to the server? – Mark Wojciechowicz Dec 23 '14 at 11:22
  • I copy following files from the bin\Deployment to the target system. 1.DTSCONFIG File 2.Integration Services Deployement Manifest 3.Integration Services Package Then i change the input and error file location and also connection string in DTSCONFIG file. Then i right click Integration Services Deployement Manifest then click Deploy. it's create the two files in given location. then i open Integration Services Package file and execute it..... – Gurunathan Dec 23 '14 at 11:48
  • I am using File system deployment only. Not SQL Server deployment.my mail id is gurun3010@gmail.com please send your mail ID. i share step-by step process i'm doing with image or i can send my source code. – Gurunathan Dec 23 '14 at 12:28
0

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.

Gurunathan
  • 97
  • 4
  • 19