none
exec ssis package as from SP throws error "peer connection closed prematurely" but works when run as job or manually RRS feed

  • Question

  • Hi,

    I have a SSIS package which has OLEDB connection to tabular model query and dumps the data into table and transforms the data for business needs.There are package parameters .I ran the package as a SQL job and in VS ,it works fine.But when i execute this as a SP ,i get very strange error like "the peer closed connection prematurely".This is the connection that points to tabular model.

    What could be causing this and what needs to be changed.Below is the script:

    /****** Object:  StoredProcedure   Script Date: 6/29/2020 23:12:41 ******/

    create proc [dbo].[RefreshOnDemand](@Yrmonth nvarchar(100),@RefreshedBy nvarchar(100)) as

    declare @my_execution_id bigint

    declare @my_folder_name varchar(255)= 'Inventory'
    declare @my_project_name varchar(255) = 'Inventory'
    declare @my_package_name varchar(255) = 'RefreshData.dtsx'
    declare @StartDate nvarchar(100),@EndDate nvarchar(100)

    set @StartDate=(select concat(@Yrmonth , '-01' ,'T00:00:00' ))
    set @EndDate=(select concat( EOMONTH(@StartDate),'T00:00:00'))


    --First create Execution and get ExecutionID in a variable

    exec [SSISDB].[catalog].[create_execution]
    @folder_name = @my_folder_name
    ,@project_name = @my_project_name
    ,@package_name = @my_package_name
    ,@reference_id = null
    ,@execution_id = @my_execution_id output


    --Wait until package execution is done
    EXEC [SSISDB].[catalog].set_execution_parameter_value
    @my_execution_id,  
    @object_type=50, 
    @parameter_name=N'SYNCHRONIZED', 
    @parameter_value=1

    --Set package parameters
    exec [SSISDB].[catalog].set_execution_parameter_value  @my_execution_id, 30, 'Month_to_delete', @Yrmonth  
    exec [SSISDB].[catalog].set_execution_parameter_value  @my_execution_id, 30, 'Scheduled_StartDate', @StartDate 
    exec [SSISDB].[catalog].set_execution_parameter_value  @my_execution_id, 30, 'Scheduled_EndDate', @EndDate
    exec [SSISDB].[catalog].set_execution_parameter_value  @my_execution_id, 30, 'WhoTrigerred', @RefreshedBy

    --Start Execution
    exec [SSISDB].[catalog].start_execution @my_execution_id

    -- Check package status, and fail script if the package failed
    IF 7 <> (SELECT [status] FROM [SSISDB].[catalog].[executions] WHERE execution_id = @my_execution_id)
    RAISERROR('The package failed. Check the SSIS catalog logs for more information', 16, 1)

    Thursday, July 2, 2020 12:04 AM

All replies

  • Hi msdnpublic1234,

    From the document: Run an SSIS package from SSMS with Transact-SQL,

    1. Make sure you logged in to the SSMS or the login using to execute the code against the Linked server is a Windows Login. If you are using SQL login this error might occur.

    2. Make sure you have the permission to the folder containing packages.

    Best Regards,

    Lily


    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, July 2, 2020 5:51 AM
  • Lily,

    I executed the SP as a TSQL script via job in the SQL server and it succeeded.I guess my login doesnt have the permission to executed the package as an SP.I will check this with DBA.Thanks .

    • Proposed as answer by Lily Lii Monday, July 6, 2020 4:39 AM
    Friday, July 3, 2020 8:11 PM
  • Hi msdnpublic1234,

    Thank you for your reply. If you find the cause of issue, it's so kind of you to come back and share it with us. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

    Best Regards,

    Lily


    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, July 6, 2020 4:39 AM