SSIS - Populate Object Variable via String Variable

Question

If I have a String Variable in SSIS that contains a list of values separated by commas (or any other standard character) - would it be possible to load those values into an object variable, so that I could then use the Object variable as a resultSet enumerator and loop through each value?

[EDIT] Just to clarify the situation - I have approx. 15 variables that each contain a 1 or 0 - each variable determines whether a particular SSAS table needs to be processed during the job (1 = Yes, it needs to be processed 0 = no, don't process it).

What I want to do now is store the table names that WERE processed in an object variable, then loop through that list, writing a log entry for each to say it was processed during this job.

[EDIT #2] I'm not explaining this very well, but let me try to summarise - here is the current situation:

I have a variable, called strPartitionArray - this variable will now contain strings, separated by comma's, determined by this expression (appreciate that this has flaws - specifically if DimAbsenceUpdate is 0, it all falls apart, but hoping to figure out a fix for that separately):

(
@[User::DimAbsenceUpdate]  == 1 ?
"Absence":""
)

+ (
@[User::DimGradeUpdate]  == 1 ?
",Grade":""
)


+ (
@[User::DimContractUpdate]  == 1 ?
",Contract":""
)

+ (
@[User::DimEmployeeUpdate]  == 1 ?
" ,Employee":""
)

So, as an example, right now, that variable's value is: Absence,Grade,Contract

I need this stored in an Object Variable, so that I can loop through those 3 entries (Absence,Grade,Contract) as part of a SQL Task, so, using the code below, I am attempting to split out those 3 words into an Object called RecordProcessDateTime

    public void Main()
    {
        String tmp = Dts.Variables["User::strPartitionArray"].ToString();

        String[] tmpArray = tmp.Split(',');

        Dts.Variables["User::RecordProcessDateTime"].Value = tmpArray;

        Dts.TaskResult = (int)ScriptResults.Success;
    }

Assuming the code above works, I then want to use RecordProcessDateTime as the enumerator for a Foreach Loop - running through each of those 3 words. The SQL Task within the container will write a log entry. Part of that SQL Task writes the word in question (Absence, Grade and then Contract) to a log Entry - that basically let's me know that the absence SSAS table was processed.

So, I've put the Script, the Foreach Loop (containing the SQL task) all into a sequence container. When I run the sequence container, it works(!) but only writes one entry to the log (when it should be writing 3) and that entry does not include any of the 3 words, it instead includes this: 'Microsoft.SqlServer.Dts.Runtime.Variable'

My assumption, is that the script is not actually putting those 3 values into the Object variable as expected, which is why the foreach loop only runs once and doesn't include any of the words.

Yes you can do this.

KeithL
– KeithL

2017-10-12 19:04:46 +00:00
Commented Oct 12, 2017 at 19:04 — KeithL
– KeithL, Commented Oct 12, 2017 at 19:04

dbencs · Accepted Answer · 2017-10-13 11:35:30Z

0

If you have a string variable in SSIS, that is well formed as you say, you can simply read the whole string into one string

String tmp = Dts.Variables["User::DimAbsence"].ToString();

then simply Split it into an array of Strings

String[] tmpArray = tmp.Split(null); /* null means the default separator (whitespace)*/

This way you can access the variables corresponding for the same logical object from SSIS at the same index.

The second part of you question is little strange to me, if you clarify it to me, I'll be at your help.

answered Oct 13, 2017 at 11:35

dbencs

743 bronze badges

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

8 Comments

Celador Over a year ago

I have used what you've suggested above, and have re-edited my post to update the point where I'm now stuck.

dbencs Over a year ago

Your method of populating the array is not quite good, since if there is no Absence for some reason , the string will beginn with a "," and then the first element of the object is going to be null, cause it separates elements between ",".

Celador Over a year ago

Yeah, I appreciate that, I figured that if I could get the rest of it working, then I could work out how to fix that later on! (perhaps not the best method of development, but I assumed I'd be able to come up with a fix). What I've done for now, is just permanently set the Absence variable to be = 1 so that it's always there (until I figure a workaround)

dbencs Over a year ago

So you are looking for a method to generate the string correctly ?

Celador Over a year ago

Not really, I figure that I can work out how to generate a proper string, that always works, no matter if Absence is there or not. For now, the part I'm having trouble with, is that even if the string I generate is fine (e.g. String = Absence,Grade,Contract) I can't get the rest of it to work - it appears as if it isn't loading that string into the Object Variable using the C# code I adapted from your earlier post.

|

KeithL · Accepted Answer · 2017-10-12 19:08:30Z

0

Use this link:

SSIS Script Task: Populate object variable from Array

And populate your array with Split function.

answered Oct 12, 2017 at 19:08

KeithL

5,6193 gold badges23 silver badges30 bronze badges

1 Comment

Celador Over a year ago

Hi @KeithL - I read through that post, and the link that was posted within it, and then went down a massive rabbit hole of c# (of which I know absolutely nothing) - ended up attempting a slightly different solution, which I've outlined in my original post as an Edit - hoping you can help get this working

Collectives™ on Stack Overflow

SSIS - Populate Object Variable via String Variable

2 Answers 2

8 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related