May 8, 2012 at 8:50 am
I have a SSIS package that I am try to execute via the stored procedures found in SSISDB.
Here is my T-SQL:
DECLARE@execution_id BIGINT
EXEC [SSISDB].[catalog].[create_execution]
@package_name = N'PackageName.dtsx'
, @execution_id = @execution_id OUTPUT
, @folder_name = N'FolderName'
, @project_name = N'ProjectName'
, @use32bitruntime = False
, @reference_id = NULL
SELECT
@execution_id
DECLARE@var0 SMALLINT = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
@execution_id
, @object_type = 50
, @parameter_name = N'LOGGING_LEVEL'
, @parameter_value = @var0
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
@execution_id
, @object_type = 50
, @parameter_name = N'SYNCHRONIZED'
, @parameter_value = 1
EXEC [SSISDB].[catalog].[start_execution]
@execution_id
GO
It says that it executes successfully, but when we check the tables where we are expecting the new records to be there is nothing. Although, if we just run the package via SSDT, all the records are there.
Am I missing something stupid? We have tried calling this code from within our web app, and via SSMS, but niether "work".
I am running SQL 2012 (obviously), I have granted the test user (that is calling the package) Read, Modify, Execute, and Manage Permissions on the Project, as well as Read, Execute Objects, and Read Objects on the Folder. These didn't help, the user continually got this message:
Msg 27146, Level 16, State 1, Procedure create_execution, Line 167
Cannot access the package or the package does not exist. Verify that the package exists and that the user has permissions to it.
Not until I added the user to the SSIS_Admin role was he able to execute the code, which as stated just returned that it was successful.
Thanks in advance for you help.
May 8, 2012 at 11:20 pm
What do the logs and the build-in reports say?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 29, 2012 at 1:54 pm
Sorry for the delayed response:
In the All Executions report I don't show anything: like it never executed, which makes sense given the error...
Under the All operations report, I just see that the Execution failed
I don't see anything indicating the failure in the logs (SQL Server, SQL Agent, Application, or System)
June 4, 2013 at 1:24 am
Please provide folder name and Project name
E.g as below
Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution]
@package_name = N'Master.dtsx',
@execution_id = @execution_id OUTPUT,
@folder_name = N'CTP3',
@project_name = N'20110712 Logging Demo',
@use32bitruntime = False,
@reference_id = 3
Select @execution_id
DECLARE @var0 smallint = 3
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
@execution_id,
@object_type = 50,
@parameter_name = N'LOGGING_LEVEL',
@parameter_value = @var0
DECLARE @var1 bit = 0
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
@execution_id,
@object_type = 50,
@parameter_name = N'DUMP_ON_ERROR',
@parameter_value = @var1
EXEC [SSISDB].[catalog].[start_execution]
@execution_id
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply