none
SSIS Jobs in SQL 2012 RRS feed

  • Question

  • In SQL 2012 is it possible to execute SSIS packages from agent by invoking dtexec? In SQL 2008 I was running bunch of jobs that invoked dtexec.exe and called packages. I am not having much luck with it.

    In SQL 2008 I was executing packages stored in SQL Server by executing a main package that lived on the file system. In my SQL Server Agent job I would execute the main package by using the command line below:

    "c:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec" /f "\\cis-sql2008\sql\MSSQL\JOBS\dataload\dataloadMain.dtsx"

    What would be the equivalent of the command above in SQL 2012. I see that Microsoft has introduced deployment models and I am sort of confused between SSISDB and SSIS Catalog.

    Any thoughts?

    Thanks


    SQL Pro


    Wednesday, April 9, 2014 2:30 PM

Answers

  • Hi sqlstar,

    To run a package stored in SSISDB catalog database, we have three different methods:

    Method 1: Create a SQL Server Integration Services Package type job step. We can select the Package source as “SSIS Catalog”, select the SSIS server, and navigate to the target package in the SSISDB Catalog.

    Method 2: Create an Operating system (CmdExec) type job step. No matter in SSIS 2012 or another version of SQL Server, we use the DTExec utility to execute a package (although SSIS runtime component is also required). For a package stored in SSISDB, we can still use the DTExec command lines to execute the package. For example, in a CmdExec job step, we can use the following command line:

    DTExec /ISSERVER "\SSISDB\ISProject\Package.dtsx" /SERVER "\."

    Method 3: Create a Transact-SQL script (T-SQL) type job step. In the T-SQL script, we use the catalog.start_execution stored procedure provided by SSISDB catalog database to start a package execution.

    Regards,
    Mike Yin

    If you have any feedback on our support, please click here


    Mike Yin
    TechNet Community Support


    Thursday, April 17, 2014 4:02 PM
    Moderator
  • Try:

    c:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\dtexec

    BOL on SQL Server 2012 dtexec: http://technet.microsoft.com/en-us/library/hh231187.aspx


    Kalman Toth Database & OLAP Architect Free T-SQL Scripts
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

    Wednesday, April 9, 2014 4:01 PM

All replies

  • One other thing that I tried to do is execute my package (not the main package but one that does the actual work) from a stored procedure. I deployed the package to SSISDB database from data tools.

    I created following stored proc:

    create procedure dbo.execute_ssis_package_DataRefresh
     @output_execution_id bigint output
    as
    begin
     declare @execution_id bigint
     exec ssisdb.catalog.create_execution
      @folder_name = 'DataRefresh'
     ,@project_name = 'xyzDataRefresh'
     ,@package_name = 'xyzData_Refresh.dtsx'
     ,@execution_id = @execution_id output
     exec ssisdb.catalog.start_execution @execution_id
     set @output_execution_id = @execution_id
    end

    then I execute the stored procedure:

    declare @output_execution_id bigint
    exec dbo.execute_ssis_package_DataRefresh @output_execution_id output
    print @output_execution_id

    but execution fails. Following query shows that execution failed with a status of 4 :

    select * from SSISDB.catalog.executions

    Here is the article that I used as a reference to execute SSIS package this way:

    http://www.mssqltips.com/sqlservertip/2992/how-to-execute-an-integration-services-ssis-package-from-a-sql-server-stored-procedure/


    SQL Pro

    Wednesday, April 9, 2014 3:08 PM
  • Try:

    c:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\dtexec

    BOL on SQL Server 2012 dtexec: http://technet.microsoft.com/en-us/library/hh231187.aspx


    Kalman Toth Database & OLAP Architect Free T-SQL Scripts
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

    Wednesday, April 9, 2014 4:01 PM
  • First check the status of package run using standard reports

    http://visakhm.blogspot.in/2012/09/easy-package-execution-monitoring-in.html

    This will give you more details on failure. Post the error messages here and we might be able to suggest something based on it


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Wednesday, April 9, 2014 4:57 PM
  • Hi sqlstar,

    To run a package stored in SSISDB catalog database, we have three different methods:

    Method 1: Create a SQL Server Integration Services Package type job step. We can select the Package source as “SSIS Catalog”, select the SSIS server, and navigate to the target package in the SSISDB Catalog.

    Method 2: Create an Operating system (CmdExec) type job step. No matter in SSIS 2012 or another version of SQL Server, we use the DTExec utility to execute a package (although SSIS runtime component is also required). For a package stored in SSISDB, we can still use the DTExec command lines to execute the package. For example, in a CmdExec job step, we can use the following command line:

    DTExec /ISSERVER "\SSISDB\ISProject\Package.dtsx" /SERVER "\."

    Method 3: Create a Transact-SQL script (T-SQL) type job step. In the T-SQL script, we use the catalog.start_execution stored procedure provided by SSISDB catalog database to start a package execution.

    Regards,
    Mike Yin

    If you have any feedback on our support, please click here


    Mike Yin
    TechNet Community Support


    Thursday, April 17, 2014 4:02 PM
    Moderator
  • Try:

    c:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\dtexec

    BOL on SQL Server 2012 dtexec: http://technet.microsoft.com/en-us/library/hh231187.aspx


    Kalman Toth Database & OLAP Architect Free T-SQL Scripts
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

    That's the 32-bit version by the way.

    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    Thursday, April 17, 2014 4:31 PM