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!

Please post the code so we can see exactly what you are doing. — Martin Smith
– Martin Smith, Commented Jun 1, 2014 at 20:41
We're also going to need to see a lot more about how you have designed your package, the Variables contained within and the tasks you're using. — billinkc
– billinkc, Commented Jun 2, 2014 at 0:38
Apologies, I wasn't able to access my actual code from work so I wrote the above. — Aaron Johnsen
– Aaron Johnsen, Commented Jun 2, 2014 at 2:01
I found a solution that works, for those who might encounter this scenario: rad.pasfu.com/index.php?/archives/… — Aaron Johnsen
– Aaron Johnsen, Commented Jun 3, 2014 at 14:41

billinkc · Accepted Answer · 2014-06-02 03:06:59Z

4

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.

answered Jun 2, 2014 at 3:06

billinkc

61.4k10 gold badges110 silver badges169 bronze badges

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

1 Comment

Aaron Johnsen Over a year ago

Billinkc, thanks for the help. I will alter my code a bit and see if that works. I'll reply back with a more detailed explanation of what I'm trying to achieve.

Collectives™ on Stack Overflow

SSIS Script Task: Populate object variable from Array

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related