XML Configuration File error in SSIS Package

Question

I've been working with SSIS packages and I'm currently stuck in this problem. I've been developing using a connection String that I wrote in a Package Variable and that value goes to the Connection Mannager through an Expression. Something like this: @[User::Connection]

It was working great for debugging. Since I needed to call some stored procedures inside Script tasks and Script Components, I would just call the Package variable as the string connection to the database. Since I'm new to the ETL world (less than a month) and I've been doing some packages (6 actually), it was just recently I read that I should save my ConnectionString in a xml file and store things like user, password there(Since production server will probbably have different user account, as mentioned here and here)

Now, I've tried to do what it says, but it doesn't work for me. So I created a new SSIS package to be testing around, it contains a Script task and its calling a simple Stored Procedure. The package Configuration contains only the ConnectionString property from the Connection Manager, and it looks something like this:

<?xml version="1.0" ?> 
- <DTSConfiguration>
- <DTSConfigurationHeading>
  <DTSConfigurationFileInfo GeneratedBy="JohnSmith" GeneratedFromPackageName="Package" GeneratedFromPackageID="{2C421533-C76C-4583-841C-367C50DB61C1}" GeneratedDate="17/01/2017 --:--:-- p.m." /> 
  </DTSConfigurationHeading>
- <Configuration ConfiguredType="Property" Path="\Package.Connections[OLEDB_CONNECTION].Properties[ConnectionString]" ValueType="String">
  <ConfiguredValue>Data Source=ServerName;User ID=UserName;Initial Catalog=DatabaseName;Provider=SQLNCLI10.1;Auto Translate=False;Application Name=SSIS-Package-{C24F1A69-A9A3-40D4-8A1E-7EA61E0EB57D}ServerName.DatabaseName.UserName;Use Encryption for Data=False;Password=abc123;</ConfiguredValue> 
  </Configuration>
  </DTSConfiguration>

I added the Password at the end, just as the link at the start suggested, and tried to get the connetionString like this in the Script task:

String cn = Dts.Connections["OLEDB_CONNECTION"].ConnectionString;

and then calling it in the Stored Procedure, like this:

 using (OleDbConnection con = new OleDbConnection(cn))
 {
     using (OleDbCommand cmd = new OleDbCommand("dbo.usp_sgn_conseguirNumDoc", con))
     {
         cmd.CommandType = CommandType.StoredProcedure;
         cmd.Parameters.Add("@var_CODDOC", OleDbType.VarChar, 3).Value = "001"; 
         cmd.Parameters.Add("@var_NUMDOC", OleDbType.BigInt).Direction = ParameterDirection.Output;
         con.Open();
         cmd.ExecuteNonQuery();
         result = Int64.Parse(cmd.Parameters["@var_NUMDOC"].Value.ToString());
     }
  }

but it fails in con.Open() so when I check the ConnectionString, its the same but without the Password value that I wrote. Its like it doesnt read it from the xml configuration file. When I checked the Message Tab in the Error List Window, I see this:

<DTS:ConnectionManager>
<DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:Property DTS:Name="ObjectName">OLEDB_CONNECTION</DTS:Property>
<DTS:Property DTS:Name="DTSID">{C24F1A69-A9A3-40D4-8A1E-7EA61E0EB57D}</DTS:Property>
<DTS:Property DTS:Name="Description"></DTS:Property>
<DTS:Property DTS:Name="CreationName">OLEDB</DTS:Property><DTS:ObjectData><DTS:ConnectionManager>
<DTS:Property DTS:Name="Retain">0</DTS:Property><DTS:Password DTS:Name="Password" Sensitive="1"></DTS:Password>
<DTS:Property DTS:Name="ConnectionString">Data Source=ServerName;User ID=UserName;Initial Catalog=DatabaseName;Provider=SQLNCLI10.1;Auto Translate=False;Application Name=SSIS-Package-{C24F1A69-A9A3-40D4-8A1E-7EA61E0EB57D}ServerName.DatabaseName.UserName;Use Encryption for Data=False;</DTS:Property></DTS:ConnectionManager></DTS:ObjectData></DTS:ConnectionManager>

Im out of ideas on what to do, any help will be appreciated

Im using Visual Studio 2008 and SQL Server 2008.

Did you link your package to the config file like this: technet.microsoft.com/en-us/library/ms140213(v=sql.105).aspx — Nick.McDermaid
@Nick.McDermaid I did, and only linked the Connection String property, from Connection Managers — Mokz

Mokz Mokz · Accepted Answer · 2017-01-18T16:18:57

Finally, after a couple of days busting my head to the desk, managed to do it. It's a silly solution, but it wouldn't have crossed my mind in a thousand years! this question helped me, not the accepted answer but this part right here:

You can store the password in the configuration string by going to properties and adding password=yourpassword but very important put a space before 'password' word and after ';'

The fact that it worked blew my mind, something so simple and yet hard to find. After that, in a Script task I would use

String cn = Dts.Connections["OLEDB_CONNECTION"].ConnectionString;

and inside a Script Component (Data Flow Task) I had to configure the connection Manager tab (gave it the same name: OLEDB_CONNECTION), and then inside the script, I would use:

String cn = This.Connections.OLEDBCONNECTION.ConnectionString;

and it worked just fine!

XML Configuration File error in SSIS Package

1 Answers