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? — , Oct 28 '16 at 13:53
Yes, as said at the bottom of my post, i set the Variable as ReadWriteVarialbe in the Script Task Wizard — Vincent G, Oct 28 '16 at 13:55
From the error, it does not seem like the problem is with the code you have here, but rather with the way your script is defined. Could you give us the code at the start of the script, with the class declaration, maybe all the way down to where you define Main. — , Oct 28 '16 at 14:01
The definition of your script looks fine. So that should not be the problem. — , Oct 28 '16 at 14:32
Wait a minute... what is this... namespace ST_c6399821104c42c2859b7b2481055848 { — , Oct 28 '16 at 14:53
You are of course right that if the variable did not exist, as named, the code inside the if statement would not execute. I am sorry that I am unable to figure this out. I have tried all I can. The error code looks like your script never even gets off the ground. Like the scripting engine is not even able to execute the script. You might try copying the code inside your main into a new script. Maybe something got corrupted during the creation of the script. Beyond that, I can't see anything wrong with what you have. Do let me know when you resolve this. — , Oct 28 '16 at 15:05
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, Oct 28 '16 at 19:52

Cleptus · Answer 1 · 2016-10-28T19:58:41.523

1

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()

edited Oct 28 '16 at 19:58

answered Oct 28 '16 at 19:52

Cleptus

3,446
4
28
34

Hello, thanks for the answer. I have tried to store the message into a variable in my `catch{}` statement, with an exception called `e`. Then tried to set the variable with the following code : `Dts.Variables["User::Error"].Value = e.Message;` But the same errors are returned by the SSIS Job. It looks like I miss something that allows me to store values into SSIS Variables. – Vincent G Oct 31 '16 at 10:32

Vincent G · Accepted Answer · 2019-04-17T07:57:04.527

My error was :

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

I had to use : Dts.Variables[].Value.ToString() instead of Dts.Variables[].ToString()

As I was using the name of the Variable instead of the content of the Variable, the GetFiles returned a null object, and it couldn't be stored inside my variable, creating the different errors.

Affect SSIS Object variable in a C# Script Task

2 Answers2