SSIS The variable cannot be found

Question

I want to execute package from visual studio solution

Code:

private Microsoft.SqlServer.Dts.Runtime.Package pkgPaquete;
private Application appAplicacion;

public DTSExecResult EjecucionPaquete(string str_Paquete, List < CatVariablesEtl > Vars = null) {

  DTSExecResult respuesta;
  try {
    appAplicacion = new Application();

    appAplicacion.PackagePassword = "pass";

    pkgPaquete = appAplicacion.LoadPackage(str_Paquete, null);
    foreach(CatVariablesEtl item in Vars) {
      pkgPaquete.Variables[item.str_NombreVariable.ToString()].Value = item.str_ValorVariable.ToString();
    }

    respuesta = pkgPaquete.Execute();

    return respuesta;
  } catch (Exception ex) {

    throw new NotImplementedException();
  }

}
}
}

It stop into foreach statement just in this line it throw catch:

pkgPaquete.Variables[item.str_NombreVariable.ToString()].Value = item.str_ValorVariable.ToString();

str_NombredeVariable value:

item.str_ValorVariable value:

Parameters into package:

Error:

The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.

I read another relationed questions but I don´t found some correct answer to my problem. Help is very appreciated. Regards

Update: as Hadi comment I try to use

var setValueParameters = new Collection<PackageInfo.ExecutionValueParameterSet>();

But I just get vs red mark, I need to import some reference?

As the second answer of Hadi I cannot reference pkgPaquete.Variables:

I suggest you inspect `pkgPaquete.Variables` at runtime and see what kind of format is required for the argument. — Nick.Mc, May 26 '17 at 01:09
Parameters cannot be modified programmatically, use variables instead of them. (you can only modify variables that start with `User::` , parameters are variables but with different scope `Package::` or `project::` and cannot be modified only when building the package) — Yahfoufi, May 29 '17 at 03:09

Hadi · Accepted Answer · 2017-05-26T18:33:00.193

1

First Attempt

Try removing User:: from the variable name

foreach(CatVariablesEtl item in Vars) {
  pkgPaquete.Variables[item.str_NombreVariable.ToString().Replace("User::","")].Value = item.str_ValorVariable.ToString();
}

Second Method to assing variables

Also try using this method to assign value to variables:

Microsoft.SqlServer.Dts.RunTime.Variables myVars = pkgPaquete.Variables;

foreach(CatVariablesEtl item in Vars) {
  myVars[item.str_NombreVariable.ToString().Replace("User::","")].Value = item.str_ValorVariable.ToString();
}

Microsoft.SqlServer.Dts.Runtime.DTSExecResult results = pkgPaquete.Execute(null, myVars, null, null, null);

if using parameters, It look like that they cannot be modified programatically. Try using variables instead of them

edited May 26 '17 at 18:33

answered May 25 '17 at 22:05

Hadi

36,233
13
65
124

I try it but I get issue: Changing the Value of a variable failed because it is a parameter variable. Parameter variables are read-only – Gerardo May 25 '17 at 22:10
@Gerardo what have you tried, removing `User::` or the second method? – Hadi May 25 '17 at 22:15
Yes I want to remove it but I have issue: Changing the Value of a variable failed because it is a parameter variable. Parameter variables are read-only – Gerardo May 25 '17 at 22:17
@Gerardo i provided a new method check it out – Hadi May 25 '17 at 22:23
I need to reference some there? I get vs red mark into Collection Note: Update in my question – Gerardo May 25 '17 at 22:29
i apologize i found that this is another reference that `ManagedDTS.dll` it is using `Microsoft.SqlServer.Management.IntegrationServices.dll` so i don't think it will work (it is using another class) – Hadi May 25 '17 at 22:33
@Gerardo i didn't find anything new, it is a shame i couldn't help. just try the second method without removing `User::` as a final attempt. – Hadi May 25 '17 at 22:39
I try to import pkgPaquete.Variables as you say but I can´t, can you check my last edit in my question please? – Gerardo May 25 '17 at 22:43
You have to call this line after loading the package, also the package parameter doesn't starts with `User::` prefix, they starts with `Package::` prefix, Variables starts with `User::` – Hadi May 25 '17 at 22:47
same error: Changing the Value of a variable failed because it is a parameter variable. Parameter variables are read-only. – Gerardo May 25 '17 at 22:57
It look like that parameters cannot be modified programatically. Try using variables instead of them – Hadi May 25 '17 at 23:29
your last comment solved foreach problem, I changed parameters fo variables and it pass foreach statement. Problem now is when I try to execute package at respuesta = pkgPaquete.Execute();, it return status fail – Gerardo May 26 '17 at 15:29
If the status is fail you have to debug your package. It looks like the problem you asked for is solved. Try debugging the package using the same variables values – Hadi May 26 '17 at 15:38
1

I debug it and I get: run a SSIS package outside of SQL Server Data Tools you must install Derived Column of Integration Services or higher, so now I try to download sql server 2014 (SP2) to try it – Gerardo May 26 '17 at 16:31
1

@gerardo you have to add a new question and accept this answer because it solved ur issue – Hadi May 26 '17 at 18:31
@Gerardo you have to accept this answer, and post a new question with the new errors. (you can read them from `pkgPaquete.errors`) – Yahfoufi May 29 '17 at 03:11

SSIS The variable cannot be found

1 Answers1

First Attempt

Second Method to assing variables