locked
Business rule to execute SSIS package OR SP RRS feed

  • Question

  • Is there a way to trigger SSIS package OR Stored Procedure by Business Rule?

    I would like to trigger SSIS package based on value change of specific column in MDS.


    RaghuM

    Wednesday, August 6, 2014 10:51 PM

Answers

  • Hi MSRN,

    As you may know, Business Rule (Master Data Services, MDS) have the following types of actions

    1. Default value actions
    2. Change value actions
    3. Validation actions
    4. External action

    The first 3 type of actions can only affect objects inside MDS. The last one, External action, has the action "start workflow".

    The "start workflow" action can be the breakthrough point for us to trigger SSIS packages or Stored Procedure(SP).

    • In the StartWorkflow method, we can load an SSIS package by SSIS object model class "Application". We can then Execute the package by package.Execute().
    • Same to start SSIS package, we can execute SP by SQLCommand from StartWorkflow method.

    To understand how it works well, please:

    Thanks,
    Jinchun Chen

    • Marked as answer by Charlie Liao Monday, August 25, 2014 6:43 AM
    Tuesday, August 12, 2014 2:42 AM

All replies

  • Hi MSRN,

    As you may know, Business Rule (Master Data Services, MDS) have the following types of actions

    1. Default value actions
    2. Change value actions
    3. Validation actions
    4. External action

    The first 3 type of actions can only affect objects inside MDS. The last one, External action, has the action "start workflow".

    The "start workflow" action can be the breakthrough point for us to trigger SSIS packages or Stored Procedure(SP).

    • In the StartWorkflow method, we can load an SSIS package by SSIS object model class "Application". We can then Execute the package by package.Execute().
    • Same to start SSIS package, we can execute SP by SQLCommand from StartWorkflow method.

    To understand how it works well, please:

    Thanks,
    Jinchun Chen

    • Marked as answer by Charlie Liao Monday, August 25, 2014 6:43 AM
    Tuesday, August 12, 2014 2:42 AM
  • Thanks Chen. 

    I would like to execute SSIS package OR SP during change value action. Is there a way to implement this while user changes the value of an attribute?


    RaghuM

    Tuesday, August 12, 2014 5:06 AM
  • Hi MSRN,

    "Change value action" is to change the value one(many) attribute(s) to another one. It is an action.

    Did you mean, you want to execute SSIS package or SP once a value of an attribute was changed? If so, please set the condition as: If XXX <operator> target value
    And then, set the action as "External Action".

    If you expectation is really "Executing SSIS package or SP in Change value actions", then you should use "External Action" instead, and in the external action, call MDS's web service to change attributes' values as well.

    Thanks,
    Jinchun Chen

    Tuesday, August 12, 2014 5:24 AM
  • In short, the "External Action" is the only action that can be used to execute SSIS package or SP, as far as I know.
    Tuesday, August 12, 2014 5:27 AM