best way to trigger DTSX Package from Biztalk


  • 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.


    Mohit Gupta

    Tuesday, November 26, 2013 10:34 AM


  • Hi Mohit,

    Calling SSIS through SQL stored procedure from BizTalk:



    • 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


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

    Create SQL  job , then trigger this job from BizTalk orchestration


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


    • 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