Search code examples
sql-serverssisetlsql-server-data-toolsssis-2012

Set package path in "Execute Package Task" From Integration Service Catalog in SSIS 2012

I would like to call SSIS package dynamically based on the configuration of various packages. I have deployed SSIS 2012 solution on Integration Service Catalog.

At run time I am having the the package name but unable to set the path of SSIS package from Integration Catalog. I am able to do this from file location but as I am using 2012 so need to use Integration Service.

Solution

  • You have to use Expressions to achieve this.

    Example

    Assuming that:

    1. The packages are located in the following Catalog:

      MSDB\MyPackages\Import\

    2. The Package Name is stored in the variable @[User::Variable]

    Solution

    First of all, create an OLEDB Connection manager to connect to the SQL Server instance related to the Integration Services Catalog.

    Add the Execute Package Task and configure it To run package from SQL Server , and use the connection you have created.

    enter image description here

    On the Execute Package Task (just click on the task and press F4 to show properties Tab) go to Expressions.

    Set The Package name Expression To:

    "\\MyPackages\\Import\\" + @[User::Variable]

    enter image description here

    Side Note: I assumed that all package are stored in MSDB\MyPackages\Import\ otherwise you have to store the full path inside the variable