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: 247037 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?

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: 247037 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?

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: 247037 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.

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: 247037 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.

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: 247037 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:

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: 247037 More actions · Answer 11

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