Datatype used by SSIS object variable

Question

I've populated a List in a script task with a custom class that has 3 properties.

I'd like to make the properties available to variables in a Foreach Loop Container in SSIS.

What c# datatypes are acceptable for the SSIS object variable? I haven't had much luck converting the list to an array (it works fine if I only use 1 property but fails as soon as I use two).

EDIT: If I populate an array like this:

string[] newArrayB = new string[] { "A1", "A2", "A3" };

I can then pass the array to an SSIS object variable and loop through it with a Foreach From Variable Enumerator. Mapping the 1 column to a SSIS string variable.

If I populate an array like this:

string[,] newArrayA = new string[3, 2] { { "A1", "Account A1" }, { "A2", "Account A2" }, { "A3", "Account A3" } };

I can then pass the the array to an SSIS object variable but can't loop through if I map the two columns to different SSIS string variables on index 0 and index 1. It just loops through each and every column as though it was one.

If I use an ADO Enumerator I get "Variable does not contain a valid data object".

My List originally comes from GetDirectories:

var folders = Directory.GetDirectories(FolderRoot).Where(d => Regex.IsMatch(Path.GetDirectoryName(d), "^[0-9]*")).ToList();

I then chop it out into the account number portion and and the rest of the folder name. I assumed I'd need to change a List to an Array to make it available to a Foreach.

You might look at Loop. I would do the same using ADO instead. — Tak
– Tak, Commented Aug 26, 2014 at 17:27
@t_m if I create a dummy array: string[,] newArray = new string[3, 2] { { "A", "Account A" }, { "B", "Account B" }, { "C", "Account C" } }; and assign it to an object variable I can't access the 2nd column using an ado enumerator — jidl
– jidl, Commented Aug 26, 2014 at 18:07
The array is basically a table and "A", "Account A" is record 1, "B", "Account B" is record 2, etc. In foreach loop you can access as many fields into variables. Here is an example Foreach Loop. One question .. where are getting the data to populate your array (File/Database)? — Tak
– Tak, Commented Aug 26, 2014 at 18:52

Ndech · Accepted Answer · 2014-08-27 12:41:16Z

I've experienced two different solutions for the same problem. None or them are perfect and are way too verbose for something that should have been easily doable in SSIS :

Create a ADO Dataset instead of the Array of array in your C# code and then loop on it using an ADO enumerator. The code to create the data container is more verbose but it can be done. One limitation is that none of the fields can be a complex object but that should not be an issue as all your data seems to be of string type.
Create a list of lists and loop on it using a Foreach from Variable enumeration. Store the current value (index 0) of the enumeration in a temporary Object variable. That variable holds the current inner list (e.g { "A", "Account A" }). Inside the foreach, you must place a Script task that will cast that Object variable to a list, extract the values and store them in separate variables. Something like :
```
List<String> accountMetadata = (List<String>)Dts.Variables["User::AccountMetadata"].Value;
Dts.Variables["User::CurrentFolder"].Value = accountMetadata[0];
Dts.Variables["User::CurrentAccount"].Value = accountMetadata[1];
```

Collectives™ on Stack Overflow

Datatype used by SSIS object variable

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related