Assign list to a ssis variable

Question

I tried to assign a list to a user variable , but it doesn't seems to work. Here is the code, what could be wrong?

StreamReader r = new StreamReader(Dts.Variables["User::filepath"].Value.ToString())
{
    string[] a = r.ReadLine().Split(';');
    List<string> list = new List<string>(a);             
    Dts.Variables["User::firstRow"].Value = list;
}

at System.RuntimeMethod‌Handle.InvokeMethod(O‌bject target, Object[] arguments, Signature sig, Boolean constructor) at System.Reflection.Ru‌ntimeMethodInfo.Unsaf‌eInvokeInternal(Objec‌t obj, Object[] parameters, Object[] arguments) at System.Reflection.Ru‌ntimeMethodInfo.Invok‌e(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.I‌nvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at Microsoft.SqlServer.‌Dts.Tasks.ScriptTask.‌VSTATaskScriptingEngi‌ne.ExecuteScript()

that is the error when I run the script in ssis

at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) — hln
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript() that is the error when I run the script in ssis — hln
There must be more to the message. The actual error is not anywhere in the part of the message you posted. — Tab Alleman
I know the message is not saying anything. But I dont get any more error message then it — hln

Matt Matt · Accepted Answer · 2016-08-15T18:21:39

At the moment you are trying to pass a List OBJECT into the user::firstrow variable. Is that variable object type? Do you really want to pass the entire object?

I suspect though that because your variable says "firstrow" that you actually only want the first string from the list in which case just specify the proper index.

So change:

 Dts.Variables["User::firstRow"].Value = list;

To

 Dts.Variables["User::firstRow"].Value = list[0];

Edit per your comment

based on this answer how to use list collection for "foreach" in SSIS it looks like it is possible to use a list object. I have never actually tried it, but I have passed around recordsets. But if the variable is an object then it should work to store the list according to that link. There are a few other potential issues then. 1 your User::firstrow is not listed as a ReadWriteVariable available for the script, your User::filepath is not listed as a ReadOnlyVariable... You are not testing whether there is a line to read.... Tab is right there is more to the error and I know debugging is difficult for ssis scripts. But you can use System.Windows.Forms.MessageBox.Show() to show the exception. Just wrap everything in a Try Catch Block and in the catch do MessageBox.Show(exception.TosTring()). So something like this:

using System.Windows.Forms;

try
{
}
catch (Exception ex)
{
    MessageBox.Show(ex.ToString());
    throw;
}

All of this begs the question of what you are attempting to do though because taking a list out of a script task for 1 row then pass to what a foreach loop to set variable? It would be easiest just to set the variables directly in c# script......

Assign list to a ssis variable

1 Answers