Affect SSIS Object variable in a C# Script Task

Question

I've created a simple script that I can't debug. Here is my issue : I'm looking to store the content of a directory into a variable in SSIS with Visual Studio 2015.

I've created a variable in my SSIS package, and set it's data type to Object. I've added to my package a Script Task, that contains this code :

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using System.Collections.Generic;
#endregion

namespace ST_c6399821104c42c2859b7b2481055848 {

[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase {

    public void Main() {

        string CSVFilesCompletePath;

        if (Dts.Variables.Contains("User::CSVFilesPathAbsolute") == true
            && Dts.Variables.Contains("User::CSVFilesPathRelativeCountry") == true
            && Dts.Variables.Contains("User::CSVFilesCountryObject") == true) {

            CSVFilesCompletePath = Dts.Variables["User::CSVFilesPathAbsolute"].ToString() + Dts.Variables["User::CSVFilesPathRelativeCountry"].ToString();


            String[] fileListTable = Directory.GetFiles(CSVFilesCompletePath, "*.xlsx");
            List<string> fileList = new List<string>(fileListTable);
            Dts.Variables["User::CSVFilesCountryObject"].Value = fileList;
        }

        Dts.TaskResult = (int)ScriptResults.Success;

    }
    #region ScriptResults declaration
    /// <summary>
    /// This enum provides a convenient shorthand within the scope of this class for setting the
    /// result of the script.
    /// 
    /// This code was generated automatically.
    /// </summary>
    enum ScriptResults
    {
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion
    }
}

As explained here : SSIS Script Task Get File Names and Store to an SSIS Object Variable

But this code returns the following error when I try to Start it through the SSIS Job :

Variables are correctly set in the Script Wizard as ReadWriteVariables.

The thing is that my code shows this error when I try to affect the SSIS Variable and try to put the String[] in it.

It's been a while since I did SSIS, but I seem to remember you have to set variables to be writeable by the script. Did you do that? — user4843530
Yes, as said at the bottom of my post, i set the Variable as ReadWriteVarialbe in the Script Task Wizard — Vince G
please can you put this line into a try catch clause and check up if the script throw an exception on it or there is another reason Dts.Variables["User::CSVFilesCountryObject"].Value = fileList; beacuse your code seems correct — Hadi

Cleptus Cleptus · Accepted Answer · 2016-10-28T19:52:15

Try adding a try-catch in the

Directory.GetFiles

call or in the whole body, then set the error message and/or the stack trace of the exception into a write SSIS variable for debugging purposes.

Edit: Looks like @H.Fadlallah point out the problem. You should use the Value property of the DtsVariable, not the ToString()

Affect SSIS Object variable in a C# Script Task

2 Answers