Vince G Vince G - 1 year ago 150
C# Question

Affect SSIS Object variable in a C# Script Task

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;

namespace ST_c6399821104c42c2859b7b2481055848 {

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

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 :

SSIS C# Script Error

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
in it.

Answer Source

My error was there :

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

I hade 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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download