May 22, 2015 at 8:03 am
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
May 22, 2015 at 8:24 am
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
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 22, 2015 at 8:41 am
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
May 22, 2015 at 10:50 am
Try running SSMS as administrator...that may solve the folder permissions issue.
May 23, 2015 at 2:38 am
Quick question, are you using absolute or relative path?
😎
May 26, 2015 at 1:54 am
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
June 4, 2015 at 3:32 am
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