none
SSIS

    Dotaz

  • how do I get package name, taskname, start_datetime, end_datetime, no of rows inserted, no of rows updated,no of rows downloaded from source after every iteration of for leach loop and insert these values into a table in sql server after each iteration.??
    čtvrtek 12. července 2018 9:53

Odpovědi

  • Can you explain a bit more on OnPostExecute or give me some guidelines on where to go through it. Like I am learning this tool newly and I have very less knowledge on event handlers

    Go to event handlers tab and then in the select dropdown select the relevant tasks. Then in the events tab select OnPreExecute event. It will enable the event. Then inside the event you can add tasks you want to capture the values like ExecuteSQL Task, Script Task etc


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Označen jako odpověď Revati_k pondělí 16. července 2018 4:12
    pátek 13. července 2018 13:44
  • Hi Revati_k,

    You need to capture these information to corresponding variables, then add another Data Flow Task followed the previous Data Flow Task inside For Each Container. In this way, you can insert the execution information into your table per loop.

    For example:

    1.You can get package name with System Variable: @[System::PackageName]

    2. To get start_datetime and end_datetime, I would suggest add two Expression Task with GETDATE() function which capture the current time then pass them to user variables: @[User::Start_datetime], @[User::End_datetime].

    3. For rows count, you can add separate Row Count Transformation inside your business Data Flow Task then reference them in next Data Flow Task.

    4. You can use Script /Component Task to capture the current/previous Task Name into variables.

    So, your package design will look like:

    Hope this helps.

    Regards,

    Pirlo Zhang 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Označen jako odpověď Revati_k pátek 13. července 2018 4:37
    pátek 13. července 2018 2:29
    Moderátor
  • How do you do this task using event handlers??? Please help!!
    you would do the same things but inside OnPostExecute Event handler rather than in normal control flow tab. Rowcount its better to capture within data flow task itself especially when it has multiple branches 

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Označen jako odpověď Revati_k pátek 13. července 2018 11:14
    pátek 13. července 2018 11:02

Všechny reakce

  • Hi Revati_k,

    You need to capture these information to corresponding variables, then add another Data Flow Task followed the previous Data Flow Task inside For Each Container. In this way, you can insert the execution information into your table per loop.

    For example:

    1.You can get package name with System Variable: @[System::PackageName]

    2. To get start_datetime and end_datetime, I would suggest add two Expression Task with GETDATE() function which capture the current time then pass them to user variables: @[User::Start_datetime], @[User::End_datetime].

    3. For rows count, you can add separate Row Count Transformation inside your business Data Flow Task then reference them in next Data Flow Task.

    4. You can use Script /Component Task to capture the current/previous Task Name into variables.

    So, your package design will look like:

    Hope this helps.

    Regards,

    Pirlo Zhang 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Označen jako odpověď Revati_k pátek 13. července 2018 4:37
    pátek 13. července 2018 2:29
    Moderátor
  • Thank you

    pátek 13. července 2018 4:37
  • How do you do this task using event handlers??? Please help!!
    pátek 13. července 2018 10:16
  • How do you do this task using event handlers??? Please help!!
    you would do the same things but inside OnPostExecute Event handler rather than in normal control flow tab. Rowcount its better to capture within data flow task itself especially when it has multiple branches 

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Označen jako odpověď Revati_k pátek 13. července 2018 11:14
    pátek 13. července 2018 11:02
  • Can you explain a bit more on OnPostExecute or give me some guidelines on where to go through it. Like I am learning this tool newly and I have very less knowledge on event handlers
    pátek 13. července 2018 11:14
  • Can you explain a bit more on OnPostExecute or give me some guidelines on where to go through it. Like I am learning this tool newly and I have very less knowledge on event handlers

    Go to event handlers tab and then in the select dropdown select the relevant tasks. Then in the events tab select OnPreExecute event. It will enable the event. Then inside the event you can add tasks you want to capture the values like ExecuteSQL Task, Script Task etc


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Označen jako odpověď Revati_k pondělí 16. července 2018 4:12
    pátek 13. července 2018 13:44
  • I tired this using Event handler. But I am not able to update proper values for row count. The problem is every time I run the package I need to truncate the table. else it is showing error like already found some records. How to tackle with this?? Please help!!
    pondělí 16. července 2018 10:44