SSIS trace end of iteration in container

Question

SSIS trace end of iteration in container

Iulian -207023

SSCertifiable

Points: 7639
More actions
April 25, 2017 at 1:44 am

#342608

In SSIS I have a container with a few tasks inside. The container iterates through a rowset or files (does not meter)
I need to trace some info after each iteration, sort of :
for( x in rowset / files ){
tasks: do something
task: trace iteration
}How do I make this in SSIS ?
I tried with OnPostExecute for the container,
but it gets triggered for every task, so multiple times per iteration
or
it triggers only once per all iterations if I make it trigger on System::SourceName == container name
I need the task: trace iteration to be executed only once per iteration.
Many thanks,
Iulian

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

Iulian -207023 - Tuesday, April 25, 2017 1:44 AM
In SSIS I have a container with a few tasks inside. The container iterates through a rowset or files (does not meter)
I need to trace some info after each iteration, sort of :
for( x in rowset / files ){
tasks: do something
task: trace iteration
}How do I make this in SSIS ?
I tried with OnPostExecute for the container,
but it gets triggered for every task, so multiple times per iteration
or
it triggers only once per all iterations if I make it trigger on System::SourceName == container name
I need the task: trace iteration to be executed only once per iteration.
Many thanks,
Iulian

Have you considered adding an ExecuteSQL task to the container which writes out this info to a table somewhere?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 2

Phil Parkin - Tuesday, April 25, 2017 6:00 AM
Iulian -207023 - Tuesday, April 25, 2017 1:44 AM
In SSIS I have a container with a few tasks inside. The container iterates through a rowset or files (does not meter)
I need to trace some info after each iteration, sort of :
for( x in rowset / files ){
tasks: do something
task: trace iteration
}How do I make this in SSIS ?
I tried with OnPostExecute for the container,
but it gets triggered for every task, so multiple times per iteration
or
it triggers only once per all iterations if I make it trigger on System::SourceName == container name
I need the task: trace iteration to be executed only once per iteration.
Many thanks,
Iulian
Have you considered adding an ExecuteSQL task to the container which writes out this info to a table somewhere?

In task: trace iteration I populate a package variable.
I could make a ExecuteSQL task too, the type of task is not that important but the Event when it is triggered,

Phil Parkin SSC Guru Points: 246985 More actions · Answer 3

Iulian -207023 - Tuesday, April 25, 2017 7:31 AM
In task: trace iteration I populate a package variable.
I could make a ExecuteSQL task too, the type of task is not that important but the Event when it is triggered,

OK. So ... is your question answered, or not?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 4

Phil Parkin - Tuesday, April 25, 2017 8:49 AM
Iulian -207023 - Tuesday, April 25, 2017 7:31 AM
In task: trace iteration I populate a package variable.
I could make a ExecuteSQL task too, the type of task is not that important but the Event when it is triggered,
OK. So ... is your question answered, or not?

I am afraid that pushing info to SQL table instead of variable does not solve the issue.
Or do you mean: push all the data to SQL table and then filter only what is needed ?

Phil Parkin SSC Guru Points: 246985 More actions · Answer 5

Iulian -207023 - Tuesday, April 25, 2017 8:53 AM
I am afraid that pushing info to SQL table instead of variable does not solve the issue.
Or do you mean: push all the data to SQL table and then filter only what is needed ?

Can you please explain 'the issue'?
Writing to a variable as part of a container is trivial, so I cannot understand what the problem is.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 6

Phil Parkin - Tuesday, April 25, 2017 9:04 AM
Iulian -207023 - Tuesday, April 25, 2017 8:53 AM
I am afraid that pushing info to SQL table instead of variable does not solve the issue.
Or do you mean: push all the data to SQL table and then filter only what is needed ?
Can you please explain 'the issue'?
Writing to a variable as part of a container is trivial, so I cannot understand what the problem is.

I need to perform a task at the end of the iterator container
( any kind of task: write to variable, sqltask, ... )
similar with the pseudo code bellow

for( x in iterator ){

tasks: do something

>> task: trace iteration
}

Phil Parkin SSC Guru Points: 246985 More actions · Answer 7

Iulian -207023 - Tuesday, April 25, 2017 9:14 AM
Phil Parkin - Tuesday, April 25, 2017 9:04 AM
Iulian -207023 - Tuesday, April 25, 2017 8:53 AM
I am afraid that pushing info to SQL table instead of variable does not solve the issue.
Or do you mean: push all the data to SQL table and then filter only what is needed ?
Can you please explain 'the issue'?
Writing to a variable as part of a container is trivial, so I cannot understand what the problem is.
I need to perform a task at the end of the iterator container
( any kind of task: write to variable, sqltask, ... )
similar with the pseudo code bellow
for( x in iterator ){
tasks: do something
>> task: trace iteration
}

To write to a variable at the end of an iteration of a loop container, use the Expression Task.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 8

Phil Parkin - Tuesday, April 25, 2017 9:24 AM
Iulian -207023 - Tuesday, April 25, 2017 9:14 AM
Phil Parkin - Tuesday, April 25, 2017 9:04 AM
Iulian -207023 - Tuesday, April 25, 2017 8:53 AM
I am afraid that pushing info to SQL table instead of variable does not solve the issue.
Or do you mean: push all the data to SQL table and then filter only what is needed ?
Can you please explain 'the issue'?
Writing to a variable as part of a container is trivial, so I cannot understand what the problem is.
I need to perform a task at the end of the iterator container
( any kind of task: write to variable, sqltask, ... )
similar with the pseudo code bellow
for( x in iterator ){
tasks: do something
>> task: trace iteration
}
To write to a variable at the end of an iteration of a loop container, use the Expression Task.

Yes expression task or script task
My question is where to place this task to be processed the last one in each iteration.

In c# I place it before }
In vb I place before End Loop or Next statement

But SSIS is visual so I am not sure the flow will get at the end each time.

I will try to make a sample tomorrow.

Thanks a lot for looking into this Phil.

Phil Parkin SSC Guru Points: 246985 More actions · Answer 9

Iulian -207023 - Tuesday, April 25, 2017 10:42 AM
Yes expression task or script task
My question is where to place this task to be processed the last one in each iteration.
In c# I place it before }
In vb I place before End Loop or Next statement
But SSIS is visual so I am not sure the flow will get at the end each time.
I will try to make a sample tomorrow.
Thanks a lot for looking into this Phil.

Use Precedence Constraints to force execution order within a container:

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 10

Phil Parkin - Tuesday, April 25, 2017 11:32 AM
Iulian -207023 - Tuesday, April 25, 2017 10:42 AM
Yes expression task or script task
My question is where to place this task to be processed the last one in each iteration.
In c# I place it before }
In vb I place before End Loop or Next statement
But SSIS is visual so I am not sure the flow will get at the end each time.
I will try to make a sample tomorrow.
Thanks a lot for looking into this Phil.
Use Precedence Constraints to force execution order within a container:

Yes, this is pretty much what I need just that if ScriptTask fails then ExpressionTask will not be reached because precedence constraint is onSuccess.
Somehow I need to make Expression Task reachable N times, does not meter if any task before it fails/succeeds/completes.
Now I think that what I am really looking after is a try-catch-finally mechanism.
The screenshot you send gives me an idea:
I will put the iteration into a sequence container named "ITERATOR - do work" then I link Expression Task onCompletion.

Many thanks Phil

Phil Parkin SSC Guru Points: 246985 More actions · Answer 11

That should work well and is exactly how I would do it.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel