c# - SSIS Script Task: Populate object variable from Array

Question

In SSIS, I am using a Script Task to run a For Each loop for each *.xml file in my folder. All .xml files will have their name passed into one of two arrays, arrayA and arrayB.

At the end of the script, I am trying to run a for loop for each array, adding each stored value into a related object variable, objectA and objectB.

What is the correct syntax to populate an object variables from an array? When I try to use them outside the script task in a for each loop below (to process each file), I get a error: The type of the value being assigned to variable differs from the current variable type

    // For the sake of the question, it doesn't matter what A and B mean. I'm just                                 trying to show how the logic structured in a simplified way.

    public void Main()
    {
        // Reset Variable
        Dts.Variables["FileARecordCount"].Value = 0;

        string NotProcessedDirectory =          Dts.Variables["NotProcessedPath"].Value.ToString();
        string FileDirectory = Dts.Variables["FullPath"].Value.ToString();
        string[] files = Directory.GetFiles(FileDirectory, "*.xml");


        // Setting up our arrays which will be used to populate our object variables. 
        // Each is set to a size of 30, but this can be changed if needed.
        string[] FileAFileCollection = new string[30];
        string[] ShipmentInformationCollection = new string[30];
        int FileAIndex = 0;
        int InfoIndex = 0;


        // We're going to examine each xml file in our directory
        foreach (string file in files)
        {
            FileInfo CurrentFile = new FileInfo(file);

            // First, let's identify FileA files
            if (CurrentFile.LastWriteTime < DateTime.Now.AddMinutes(-10))
            {
                // Add each filename into an array which will populate our package object variable
                FileAFileCollection[FileAIndex] = CurrentFile.Name.ToString();
                FileAIndex++;


                // Before we move the file, let's check to see if the file exists already in the NotProcessed directory.
                if (File.Exists(NotProcessedDirectory + CurrentFile.Name))
                    File.Delete(NotProcessedDirectory + CurrentFile.Name);

                // Copy the file to the NotProcessed folder and delete the original
                CurrentFile.CopyTo(NotProcessedDirectory + CurrentFile.Name);
                CurrentFile.Delete();

                bool FileAMessage = false;
                Dts.Events.FireInformation(0, "FileA File Found", "File: " + CurrentFile.Name.ToString() + " moved to NotProcessed", string.Empty, 0, ref FileAMessage);
            }


            // If the file isn't an FileA, we want to get all Shipment Information files
            else
            {
                if (CurrentFile.Name.Substring(0, 6).ToString().ToUpper() == "FILEB")
                {


                    // Add each filename into an array which will populate our package object variable
                    ShipmentInformationCollection[InfoIndex] = CurrentFile.ToString();
                    InfoIndex++;
                }
            }
        } // End ForEach File


        // Add all of our FileA file names to our Ophan File object 
        if (FileAIndex > 0)
        {
            bool AddingFileAMessage = false;
            Dts.Events.FireInformation(0, "Adding FileA Files to Collection", FileAIndex + " file(s) added to collection", string.Empty, 0, ref AddingFileAMessage);
            Dts.Variables["FileARecordCount"].Value = FileAIndex;
            Dts.Variables["FileAFileCollection"].Value = FileAFileCollection;
        }

        // Add all of our Shipment Information file names to our Shipment Information Object 
        if (InfoIndex > 0)
        {

            Dts.Variables["ShipmentInformationCollection"].Value = ShipmentInformationCollection;
        }

    } //End Main

After this script task ends, I am going to a for each container that uses an ADO collection with ObjectVariableA as its collection, passing the current value of said variable into a string varable, FileName. To clarify, I'm using the script task to get a bunch of file names into my object that are of type "A" and loop through each file to continue my logic.

Any help is greatly appreciated. Thank you for looking!

Answer 1

It appears you are attempting to add/concatenate values in an SSIS Variable. That's not going to work for a number of reasons.

The first reason, is that the data types of SSIS Variables are roughly analogous to the .NET primitives. Therefore, the += isn't going to do what you think it will do (assuming it doesn't blow up outright).

The second reason is that you are operating on the base Object itself. Instead, you're likely looking to assign to the .Value property. That is what would be automagically accessed in a ForEach loop construct.

// illogical for SSIS
for(int i = 0; i < fileAIndex; i++)
    Dts.Variables["ObjectVariableA"] += fileA[i].toString();
// Replaced with
Dts.Variables["ObjectVariableA"].Value = fileA;

Just assign the array like object to an SSIS Variable of type Object. The Object can hold anything that's derived from object. Have a DataSet, assign it to an Object. Your own custom class, same.

That addresses the technical part of your question. I strongly suggest if you would explain what you're doing, we could find a more SSIS-ic (doesn't quite have the same impact of pythonic) way of doing things.