SSIS trace end of iteration in container

  • Iulian -207023

    SSCertifiable

    Points: 7508

    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

  • Phil Parkin

    SSC Guru

    Points: 243766

    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?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Iulian -207023

    SSCertifiable

    Points: 7508

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

    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?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Iulian -207023

    SSCertifiable

    Points: 7508

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

    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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Iulian -207023

    SSCertifiable

    Points: 7508

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

    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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Iulian -207023

    SSCertifiable

    Points: 7508

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

    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:
    https://www.sqlservercentral.com/Forums/Uploads/Images/8dc084f6-0e5f-4e86-a237-e698.JPG

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Iulian -207023

    SSCertifiable

    Points: 7508

    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:
    https://www.sqlservercentral.com/Forums/Uploads/Images/8dc084f6-0e5f-4e86-a237-e698.JPG

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

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

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

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

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