none
Schedule SP in SQL Agent RRS feed

  • Question

  • I have an stored procedure which run an ssis package.

    Below is the script

    CREATE PROCEDURE MRA
    AS
    Declare @execution_id bigint
    EXEC [SSISDB].[catalog].[create_execution] @package_name=N'CNP_FORMAT.dtsx',
    @execution_id= @execution_id OUTPUT,
    @folder_name=N'MRA',
      @project_name=N'CNP_FORMAT',
    @use32bitruntime=False,
      @reference_id=Null
    EXEC [SSISDB].[catalog].[start_execution] @execution_id
    

    When script is run manually in SQL server, targeted table are uploaded correctly.

    I have schedule above stored procedure in SQL Agent.

    Schedule Job runs without errors but targeted table NOT uploaded!!!

    Means the schedule job in SQL Agent did not run the ssis package!

    I'm currently stuck with this issue and don't know how to proceed.


    Saturday, December 14, 2019 12:18 PM

All replies

  • Is the SSIS package working correctly?

    Please view the ssis execution report and see if it failed. In your management studio, you should see integration services catalog, expand to the project and package and right click and view execution reports.

    see here : https://www.mssqltips.com/sqlservertip/4812/reporting-with-the-sql-server-integration-services-catalog/

    this is for all packages but you can go to individual package and do the same.

    if everything is looks right, make sure your destination and source are right ( correct DB\tables etc).. and your source has data to push. 



    Hope it Helps!!

    Saturday, December 14, 2019 12:47 PM
  • It is wasteful to post the same question independently to multiple forums. 
    Saturday, December 14, 2019 12:56 PM
  • You asked this same question on Stackoverflow. Please do not multi-post on different forums as it causes duplicate effort and more work for all parties involved trying to help you.



    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Saturday, December 14, 2019 12:59 PM
  • SSIS Package works fine when run manually.
    As concern for the job history its executing with success but not loading the target table.
    Saturday, December 14, 2019 1:03 PM
  • check the ssis package history for that execution id.( not the job history)

    But as others mentioned, you can schedule the ssis directly in sql agent if you do not need other additional logic.

    To keep in simple, i would do three things

    1. run the ssis package from integration services catalog manually. did it work?

    2. run this script from ssms and get the exection id and view the package execution report for this execution id 

    Declare @execution_id bigint
    EXEC [SSISDB].[catalog].[create_execution] @package_name=N'CNP_FORMAT.dtsx',
    @execution_id= @execution_id OUTPUT,
    @folder_name=N'MRA',
      @project_name=N'CNP_FORMAT',
    @use32bitruntime=False,
      @reference_id=Null
    select @execution_id
    EXEC [SSISDB].[catalog].[start_execution] @execution_id

    3. run the stored procedure. and view the execution report.

    if all three report success, i think you may want to check the ssis package and make sure you do not have any checks\logic thats causing this behavior.

    also make sure your source\destination are set right. runtime variables are changing them


    Hope it Helps!!

    Saturday, December 14, 2019 1:24 PM
  • All 3 steps working perfectly when executing manually....but don't understand why its not working while passing the stored procedure in SQL Agent though the its is displaying the job as succeeded! 
    Saturday, December 14, 2019 1:39 PM
  • Hi KAN16,

    Could you please share the execution results and the schedule of your job ?

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, December 16, 2019 6:56 AM
  • Hi,

    May I know if you have anything to update?

    Please remember to click "Mark as Answer" the responses that resolved your issue. 

    This can be beneficial to other community members reading this thread.

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, December 19, 2019 9:50 AM