Running SSIS task in SSMS Invocation error

  • Hi,

    I'm trying to run an SSIS task in the SSMS query window. The SSIS task itself requires a parameter that will likely change every time it's run, so I'm not sure an agent takes would be appropraite (unless you can change the parameter setting for a SSIS agent job dynamically when running). As a result I wanted to make a stored procedure which you could take the parameter details and call the task in SQL, whicj is as follows:

    Declare @execution_id bigint

    EXEC [SSISDB].[catalog].[create_execution] @package_name=N'ExportHPClaimForm.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'SSIS Packages', @project_name=N'SEIB Packages', @use32bitruntime=False, @reference_id=Null

    Select @execution_id

    DECLARE @var0 int = 412

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

    DECLARE @var1 smallint = 1

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

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

    GO

    When I run the task though, I get the error "Check for Directory Error: Exception has been thrown by the target of an invocation." The task itself is a Script task, which checks if a directory exists, and if not, creates it.

    I'm running the SQL as myself, and I'm a network administrator, so I have access to the directory. The user that SSIS, and the SQL Server Agent is running under also has access to this directory as well.

    The error doesn't really seem to tell me a lot, although Google seems to imply that it's a permissions error, but that doesn't make sense when both users have access.

    I have tried running the SSIS task in an Agent job and it runs fine, as it does in VS 2010, so I'm not sure why running it in SSMS would cause any problems.

    Any help would be greatly appreciated.

    Thanks.

    Thom

    Edit: I've changed the task to a File System Task, which comes back with an Access Denied error, which is odd, as all network users should have permission to that directory anyway.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • When running in SSMS I believe it is running under the SQL Server Service account. I know it is not running under the SQL Agent Service account and that is verified by the fact that you can run this successfully in a Job.

  • Jack Corbett (5/22/2015)


    When running in SSMS I believe it is running under the SQL Server Service account. I know it is not running under the SQL Agent Service account and that is verified by the fact that you can run this successfully in a Job.

    Gave it a go, gave the user full access, but no luck. Still receiving the same error.

    Have just editted my initial post, but it does seem to be a permission error, but I have no ideas why, as both the SSIS, and SQL Server users have access to that directory.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Try running SSMS as administrator...that may solve the folder permissions issue.

  • Quick question, are you using absolute or relative path?

    😎

  • Eirikur Eiriksson (5/23/2015)


    Quick question, are you using absolute or relative path?

    😎

    I'm defining the full address, with the server name, and directors, rather than the shared directory name.

    I'm already running SSMS in administrator mode, however, that wouldn't solve the issue as the script won't be run from SSMS in the final solution.

    I read something very breifly before I left work last week, that seems to imply it's to do with Kerboros, not something I'm familiar with. I'm researching into it, but if anyone reading this has some insight and thinks it could be the right path, please let me know.

    Thanks

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • I got around this in the end, by setting the task to run in as an Agent Job. This isn't really the solution I wanted, as it's now on a 5 minute schedule, rather than being run reactively, but for some reason I couldn't figure out why the script failed when run from SQL. Just means that a user gets a "your document will be ready shortly" message, and has to wait until the next time the script runs.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

Viewing 7 posts - 1 through 6 (of 6 total)

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