SSISDB deployed package run as windows user

  • Hi

    I am trying to run a SSISDB deployed package by windows user but as per My requirements I cannot directly login as windows user to server as job is being called from unix

    So I tried using SQL agent job called by sql user and putting the following code in stored procedure with execute as windows users

    the stored procedure is called from the job

    the code is standard execution method from sql

    EXEC [SSISDB].[catalog].[create_execution] @package_name=N'abc.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'pqr', @project_name=N'abc', @use32bitruntime=False, @reference_id=Null

    DECLARE @var0 smallint = 1

    EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=50, @parameter_name=N'DUMP_ON_ERROR', @parameter_value=@var0

    EXEC [SSISDB].[catalog].[start_execution] @execution_id

    But it is not able to access a shared drive part of package code during execution

    but if the package is directly run as windows user this does work fine

    Any idea

    ------------------------------------------------------------------------------------

    Ashish

  • now I have even tried putting stored procedure in ssisdb to avoid multi database clash but still the issue

    The job is called correctly but when it comes to access shared drive it cant though it does have access to it

    ------------------------------------------------------------------------------------

    Ashish

  • I would simple schedule the package with a SQL Server Agent job (use the SSIS jobstep) and use a proxy.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Hi

    I am not able to use SQL agent as this job has to be called be unix box and so cannot log in as windows user or proxy user

    So I tried execute as in stored procedure but if I explicitly mention windows user it does call the package but cannot access share folder through package

    ------------------------------------------------------------------------------------

    Ashish

  • The SSIS package needs to be stored and executed somewhere right? Create a SQL Server Agent job on that machine and start the job from the Unix box.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply