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
– user4843530, Commented Oct 28, 2016 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
– Vincent G, Commented Oct 28, 2016 at 13:55
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
– Hadi, Commented Oct 28, 2016 at 19:52

Cleptus · Accepted Answer · 2016-10-28 19:58:41Z

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, 2016 at 19:58

answered Oct 28, 2016 at 19:52

Cleptus

3,5494 gold badges31 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vincent G Over a year ago

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 · Accepted Answer · 2019-04-17 07:57:04Z

0

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.

edited Apr 17, 2019 at 7:57

answered Oct 31, 2016 at 13:10

Vincent G

3611 gold badge5 silver badges22 bronze badges

Collectives™ on Stack Overflow

Affect SSIS Object variable in a C# Script Task

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related