none
best way to trigger DTSX Package from Biztalk

    Question

  • what is best way to execute dtsx package from biztalk .

    1: use biztalk to load from file system then execute from orchestration.

    2: should be preloaded into sql instance ,use only  biztalk  for execute package(using C#).

    3: create sql job , then trigger this job from biztalk orchestration.

    4 : create stored proc for execute dtsx package ,only  use biztalk to execute stored procedure.

    please suggest the best way with all possible pros and cons.

    Regards

    Mohit Gupta

    Tuesday, November 26, 2013 10:34 AM

Answers

  • Hi Mohit,

    Calling SSIS through SQL stored procedure from BizTalk:

    Pros:

    Cons:

    • Bit complex, involves triggering sp which in turn based on the stored value trigger SSIS.

    Calling SSIS from .NET:

    Pro: Standard way for calling external objects/entities

    Cons:

    • Multiple layers : BizTalk-.NET assembly-SQL
    • No direct link between BizTalk and SSIS

    Create SQL  job , then trigger this job from BizTalk orchestration

    Pro:

    • Mmm not much, but entities involved are BizTalk and SQL

    Cons:

    • Indirection, SSIS package itself a set of steps and you introduce another SQL element (SQL-Job) which inturn to trigger another SQL object.
    • No correlation. From SSIS to BizTalk directly.
    • If any failure is SSIS can’t be easily identified in BizTalk.
    • Maintenance involve another object SQL-Job. So end with 3 objects BizTalk, SQL-Job and SSIS package.

    I would consider using calling SSIS through SQL stored procedure from BizTalk. You have some options now, see which one can suit your environment and requirement better.


    If this answers your question please mark it accordingly. If this post is helpful, please vote as helpful.




    Tuesday, November 26, 2013 11:11 AM