Created
July 6, 2013 14:03
-
-
Save kulmam92/5939978 to your computer and use it in GitHub Desktop.
Modified version of catalog.create_execution SP in SSISDB
http://speaksql.wordpress.com/2013/06/27/ssis-2012-fail-to-start-execution-timed-out/
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
USE [SSISDB] | |
GO | |
/** | |
.SYNOPSIS | |
Modified version of catalog.create_execution SP. | |
.DESCRIPTION | |
I created new version of catalog.create_execution SP to reslove the execution timed out error. | |
Permission check logic is not included. However, everything else is the same. | |
Works for 2012 and higher | |
.EXAMPLE | |
Declare @execution_id bigint | |
EXEC [SSISDB].[catalog].[create_execution_new] @package_name=N'Package.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'DEV' | |
, @project_name=N'Test2', @use32bitruntime=False, @reference_id=1 | |
Select @execution_id | |
.NOTES | |
Version History | |
v1.0 - SpeakSQL.wordpress.com - 6/20/2013 - Initial release | |
.TEST | |
Declare @execution_id bigint | |
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Package.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'DEV' | |
, @project_name=N'Test2', @use32bitruntime=False, @reference_id=1 | |
Select @execution_id | |
Declare @execution_id bigint | |
EXEC [SSISDB].[catalog].[create_execution_new] @package_name=N'Package.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'DEV' | |
, @project_name=N'Test2', @use32bitruntime=False, @reference_id=1 | |
Select @execution_id | |
exec sp_executesql N'DECLARE @var0 smallint = 1 | |
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=50, @parameter_name=N''LOGGING_LEVEL'', @parameter_value=@var0 | |
',N'@execution_id bigint',@execution_id=10009 | |
go | |
exec sp_executesql N'EXEC [SSISDB].[catalog].[start_execution] @execution_id',N'@execution_id bigint',@execution_id=10009 | |
go | |
**/ | |
CREATE PROCEDURE [catalog].[create_execution_new] | |
@folder_name nvarchar(128), | |
@project_name nvarchar(128), | |
@package_name nvarchar(260), | |
@reference_id bigint = null, | |
@use32bitruntime bit = 0, | |
@execution_id bigint output | |
WITH EXECUTE AS 'AllSchemaOwner' | |
AS | |
SET NOCOUNT ON | |
DECLARE @caller_id int | |
DECLARE @caller_name [internal].[adt_sname] | |
DECLARE @caller_sid [internal].[adt_sid] | |
DECLARE @suser_name [internal].[adt_sname] | |
DECLARE @suser_sid [internal].[adt_sid] | |
DECLARE @created_time datetimeoffset | |
DECLARE @return_value int | |
DECLARE @operation_id bigint | |
DECLARE @result bit | |
DECLARE @environment_found bit | |
DECLARE @project_id bigint | |
, @version_id bigint | |
, @environment_id bigint | |
, @environment_name nvarchar(128) | |
, @environment_folder_name nvarchar(128) | |
, @reference_type char(1) | |
, @server_edition nvarchar(255) | |
, @server_logging_level [NVARCHAR](256) | |
, @bitfalse bit | |
, @savepoint_name NCHAR(32) | |
, @tran_count INT | |
DECLARE @sqlString nvarchar(1024) | |
DECLARE @key_name [internal].[adt_name] | |
DECLARE @certificate_name [internal].[adt_name] | |
DECLARE @encryption_algorithm nvarchar(255) | |
DECLARE @env_key_name [internal].[adt_name] | |
DECLARE @env_certificate_name [internal].[adt_name] | |
DECLARE @project_key_name [internal].[adt_name] | |
DECLARE @project_certificate_name [internal].[adt_name] | |
declare @execution_parameter_values table( | |
[execution_id] [bigint] | |
, [object_type] [smallint] | |
, [parameter_data_type] [nvarchar](128) | |
, [parameter_name] [sysname] | |
, [parameter_value] [sql_variant] | |
, [sensitive_parameter_value] [varbinary](max) | |
, [base_data_type] [nvarchar](128) | |
, [sensitive] [bit] | |
, [required] [bit] | |
, [value_set] [bit] | |
, [runtime_override] [bit] | |
, [err] [int]) | |
select @use32bitruntime = case when CHARINDEX(N'64',Convert(nvarchar(255),SERVERPROPERTY('Edition')))=0 then 1 else @use32bitruntime end | |
, @bitfalse = 0 | |
, @encryption_algorithm = (SELECT [internal].[get_encryption_algorithm]()) | |
SELECT @server_logging_level = [property_value] | |
FROM [internal].[catalog_properties] | |
WHERE [property_name] = 'SERVER_LOGGING_LEVEL' | |
IF @encryption_algorithm IS NULL BEGIN | |
RAISERROR(27156, 16, 1, 'ENCRYPTION_ALGORITHM') WITH NOWAIT | |
END | |
EXECUTE AS CALLER | |
EXEC [internal].[get_user_info] | |
@caller_name OUTPUT, | |
@caller_sid OUTPUT, | |
@suser_name OUTPUT, | |
@suser_sid OUTPUT, | |
@caller_id OUTPUT; | |
IF( | |
EXISTS(SELECT [name] | |
FROM sys.server_principals | |
WHERE [sid] = @suser_sid AND [type] = 'S') | |
OR | |
EXISTS(SELECT [name] | |
FROM sys.database_principals | |
WHERE ([sid] = @caller_sid AND [type] = 'S')) | |
) | |
BEGIN | |
RAISERROR(27123, 16, 1) WITH NOWAIT | |
RETURN 1 | |
END | |
REVERT | |
IF( | |
EXISTS(SELECT [name] | |
FROM sys.server_principals | |
WHERE [sid] = @suser_sid AND [type] = 'S') | |
OR | |
EXISTS(SELECT [name] | |
FROM sys.database_principals | |
WHERE ([sid] = @caller_sid AND [type] = 'S')) | |
) | |
BEGIN | |
RAISERROR(27123, 16, 1) WITH NOWAIT | |
RETURN 1 | |
END | |
IF (@folder_name IS NULL OR @project_name IS NULL | |
OR @package_name IS NULL OR @use32bitruntime IS NULL) | |
BEGIN | |
RAISERROR(27138, 16 , 1) WITH NOWAIT | |
RETURN 1 | |
END | |
BEGIN TRY | |
SET @created_time = SYSDATETIMEOFFSET() | |
EXEC @return_value = [internal].[insert_operation] | |
200, | |
@created_time, | |
20, | |
NULL, | |
@project_name, | |
1, | |
null, | |
null, | |
@caller_sid, | |
@caller_name, | |
null, | |
null, | |
null, | |
@operation_id OUTPUT | |
IF @return_value <> 0 | |
RETURN 1; | |
EXECUTE AS CALLER | |
EXEC @return_value = [internal].[init_object_permissions] 4, @operation_id, @caller_id | |
REVERT | |
IF @return_value <> 0 BEGIN | |
RAISERROR(27153, 16, 1) WITH NOWAIT | |
RETURN 1 | |
END | |
SET @execution_id = @operation_id | |
END TRY | |
BEGIN CATCH | |
UPDATE [internal].[operations] | |
SET [end_time] = SYSDATETIME() | |
, [status] = 4 | |
WHERE operation_id = @operation_id; | |
THROW; | |
END CATCH | |
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE | |
SELECT @project_id=projs.[project_id] | |
, @version_id=projs.[object_version_lsn] | |
, @environment_id= | |
case when refs.reference_type='A' | |
then (select envs.[environment_id] | |
FROM [internal].[folders] envfds | |
INNER JOIN [internal].[environments] envs | |
on envfds.[folder_id] = envs.[folder_id] | |
where envs.[environment_name] = refs.environment_name | |
AND envfds.[name] = refs.environment_folder_name) | |
else (select envs.[environment_id] | |
FROM [internal].[environments] envs | |
where envs.[environment_name] = refs.environment_name | |
and envs.[folder_id] = projs.[folder_id]) | |
end | |
, @reference_type=refs.reference_type | |
, @environment_folder_name=refs.environment_folder_name | |
, @environment_name=refs.environment_name | |
FROM [internal].[projects] projs | |
inner join [internal].[object_versions] ver | |
on ver.[object_id] = projs.[project_id] | |
AND ver.[object_version_lsn] = projs.[object_version_lsn] | |
INNER JOIN [internal].[folders] fds | |
ON projs.[folder_id] = fds.[folder_id] | |
INNER JOIN [internal].[packages] pkgs | |
ON projs.[project_id] = pkgs.[project_id] | |
and projs.[object_version_lsn] = pkgs.[project_version_lsn] | |
left join [internal].[environment_references] refs | |
on refs.reference_id=@reference_id | |
and projs.[project_id]=refs.[project_id] | |
WHERE fds.[name] = @folder_name | |
AND projs.[name] = @project_name | |
AND pkgs.[name] = @package_name | |
and ver.[object_status] = 'C' | |
and ver.[object_type]= 20 | |
IF (@project_id IS NULL) BEGIN | |
RAISERROR(27146, 16, 1) WITH NOWAIT | |
-- Cannot access the package or the package does not exist. Verify that the package exists and that the user has permissions to it. | |
END else IF (@reference_id IS NOT NULL and @environment_id IS NULL) BEGIN | |
RAISERROR(27208, 16, 1, @reference_id) WITH NOWAIT | |
END | |
SET @key_name = 'MS_Enckey_Exec_'+CONVERT(varchar,@execution_id) | |
SET @certificate_name = 'MS_Cert_Exec_'+CONVERT(varchar,@execution_id) | |
SET @sqlString = 'CREATE CERTIFICATE ' + @certificate_name + ' WITH SUBJECT = ''ISServerCertificate''' | |
IF NOT EXISTS (SELECT [name] FROM [sys].[certificates] WHERE [name] = @certificate_name) | |
EXECUTE sp_executesql @sqlString | |
SET @sqlString = 'CREATE SYMMETRIC KEY ' + @key_name +' WITH ALGORITHM = ' | |
+ @encryption_algorithm + ' ENCRYPTION BY CERTIFICATE ' + @certificate_name | |
IF NOT EXISTS (SELECT [name] FROM [sys].[symmetric_keys] WHERE [name] = @key_name) | |
EXECUTE sp_executesql @sqlString | |
SET @sqlString = 'OPEN SYMMETRIC KEY ' + @key_name | |
+ ' DECRYPTION BY CERTIFICATE ' + @certificate_name | |
EXECUTE sp_executesql @sqlString | |
IF @environment_id IS NOT NULL BEGIN | |
SET @env_key_name = 'MS_Enckey_Env_'+CONVERT(varchar,@environment_id) | |
SET @env_certificate_name = 'MS_Cert_Env_'+CONVERT(varchar,@environment_id) | |
SET @sqlString = 'OPEN SYMMETRIC KEY ' + @env_key_name | |
+ ' DECRYPTION BY CERTIFICATE ' + @env_certificate_name | |
EXECUTE sp_executesql @sqlString | |
END | |
SET @project_key_name = 'MS_Enckey_Proj_'+CONVERT(varchar,@project_id) | |
SET @project_certificate_name = 'MS_Cert_Proj_'+CONVERT(varchar,@project_id) | |
SET @sqlString = 'OPEN SYMMETRIC KEY ' + @project_key_name | |
+ ' DECRYPTION BY CERTIFICATE ' + @project_certificate_name | |
EXECUTE sp_executesql @sqlString | |
INSERT INTO @execution_parameter_values | |
( [execution_id],[object_type],[parameter_data_type],[parameter_name],[parameter_value] | |
, [sensitive_parameter_value] | |
, [base_data_type],[sensitive],[required],[value_set],[runtime_override],err) | |
select params.execution_id | |
, params.[object_type] | |
, params.[parameter_data_type] | |
, params.[parameter_name] | |
, case when [sensitive] = 0 AND [required] = 0 AND [value_set] = 0 | |
then params.[design_default_value] | |
else params.[parameter_value] end [parameter_value] | |
, case when params.[sensitive] = 1 AND params.[parameter_value] IS NOT NULL AND params.[sensitive_parameter_value] IS NULL AND params.[parameter_data_type] = 'datetime' | |
then EncryptByKey(KEY_GUID(@key_name),CONVERT(varbinary(4000),CONVERT(datetime2,params.parameter_value))) | |
when params.[sensitive] = 1 AND params.[parameter_value] IS NOT NULL AND params.[sensitive_parameter_value] IS NULL AND params.[parameter_data_type] IN ('double', 'single', 'decimal') | |
then EncryptByKey(KEY_GUID(@key_name),CONVERT(varbinary(4000),CONVERT(decimal(38,18),params.parameter_value))) | |
when params.[sensitive] = 1 AND params.[parameter_value] IS NOT NULL AND params.[sensitive_parameter_value] IS NULL AND params.[parameter_data_type] NOT IN ('datetime', 'double', 'single', 'decimal') | |
then EncryptByKey(KEY_GUID(@key_name),CONVERT(varbinary(4000),params.[parameter_value])) | |
else ENCRYPTBYKEY(KEY_GUID(@key_name), DECRYPTBYKEY(params.[sensitive_parameter_value])) | |
end [sensitive_parameter_value] | |
, params.[base_data_type] | |
, params.[sensitive] | |
, params.[required] | |
, params.[value_set] | |
, params.[runtime_override] | |
, params.err | |
from ( | |
SELECT @execution_id execution_id, | |
params.[object_type], | |
params.[parameter_data_type], | |
params.[parameter_name], | |
case when params.[value_type] = 'V' then | |
case when params.sensitive=0 then params.[default_value] end | |
else | |
case when vars.sensitive=0 then vars.[value] end | |
end [parameter_value], | |
case when params.[value_type] = 'V' then | |
case when params.sensitive=1 then ENCRYPTBYKEY(KEY_GUID(@key_name), DECRYPTBYKEY(sensitive_default_value)) end | |
else | |
case when vars.sensitive=1 then ENCRYPTBYKEY(KEY_GUID(@key_name), DECRYPTBYKEY(vars.[sensitive_value])) end | |
end [sensitive_parameter_value], | |
case when params.[value_type] = 'V' then params.[base_data_type] | |
else vars.[base_data_type] end [base_data_type], | |
case when params.[value_type] = 'R' and vars.[sensitive] = 1 then vars.[sensitive] | |
else params.[sensitive] end [sensitive], | |
params.[required], | |
case when params.[value_type] = 'R' and vars.[name] is null then 0 | |
else params.[value_set] end [value_set], | |
0 [runtime_override], | |
params.[design_default_value], | |
case when params.[value_type] = 'R' AND params.[parameter_data_type] <> vars.[type] then 27148 | |
when params.[value_type] = 'R' AND params.[parameter_data_type] = vars.[type] | |
AND params.[sensitive] =0 AND vars.[sensitive] = 1 then 27221 | |
else 0 end err | |
FROM [internal].[object_parameters] params | |
left join [internal].[environment_references] refs | |
on refs.reference_id=@reference_id | |
and params.[project_id]=refs.[project_id] | |
left JOIN [internal].[environment_variables] vars | |
ON params.[referenced_variable_name] = vars.[name] | |
AND params.[value_type] = 'R' | |
AND vars.[environment_id] = @environment_id | |
WHERE params.[project_id] = @project_id | |
AND (params.[object_type] = 20 | |
OR (params.[object_name] = @package_name | |
AND params.[object_type] = 30)) | |
AND params.[project_version_lsn] = @version_id | |
) params | |
select @return_value=max(err) | |
from @execution_parameter_values | |
if @return_value<>0 begin | |
RAISERROR(@return_value , 16 , 1) WITH NOWAIT | |
end | |
SET TRANSACTION ISOLATION LEVEL READ COMMITTED | |
set @tran_count = @@TRANCOUNT; | |
IF @tran_count > 0 BEGIN | |
SET @savepoint_name = REPLACE(CONVERT(NCHAR(36), NEWID()), N'-', N''); | |
SAVE TRANSACTION @savepoint_name; | |
END | |
ELSE | |
BEGIN TRANSACTION; | |
BEGIN TRY | |
--get an app lock to make sure the transaction is executed exclusively | |
-- When the lock_owner value is Transaction, by default or specified explicitly, sp_getapplock must be executed from within a transaction. | |
--DECLARE @lock_result int | |
--EXEC @lock_result = sp_getapplock | |
-- @Resource = 'MS_ISServer_Create_Execution', | |
-- @LockTimeOut= 5000, -- 5 seconds | |
-- @LockMode = 'Exclusive' | |
--IF @lock_result < 0 | |
--BEGIN | |
-- RAISERROR(27195, 16, 1) WITH NOWAIT -- raise the timeout error | |
--END | |
UPDATE [internal].[operations] | |
SET [object_id] = @project_id | |
WHERE [operation_id] = @operation_id | |
IF @@ROWCOUNT <> 1 BEGIN | |
RAISERROR(27112, 16, 1, N'operations') WITH NOWAIT | |
END | |
INSERT into [internal].[executions]( | |
execution_id,folder_name,project_name,package_name,reference_id,reference_type, | |
environment_folder_name,environment_name,project_lsn,executed_as_sid, | |
executed_as_name,use32bitruntime) | |
VALUES (@operation_id,@folder_name,@project_name,@package_name,@reference_id,@reference_type, | |
@environment_folder_name,@environment_name,@version_id,@caller_sid, | |
@caller_name,@use32bitruntime) | |
INSERT INTO [internal].[execution_parameter_values] | |
( [execution_id],[object_type],[parameter_data_type],[parameter_name],[parameter_value] | |
, [sensitive_parameter_value] | |
, [base_data_type],[sensitive],[required],[value_set],[runtime_override]) | |
select [execution_id],[object_type],[parameter_data_type],[parameter_name],[parameter_value] | |
, [sensitive_parameter_value],[base_data_type],[sensitive],[required],[value_set],[runtime_override] | |
from @execution_parameter_values | |
INSERT INTO [internal].[execution_parameter_values] | |
( [execution_id],[object_type],[parameter_data_type],[parameter_name],[parameter_value] | |
, [base_data_type],[sensitive],[required],[value_set],[runtime_override]) | |
VALUES | |
(@execution_id,50,'Boolean','DUMP_ON_ERROR',CONVERT(sql_variant,@bitfalse),'bit',0,0,1,0), | |
(@execution_id,50,'Boolean','DUMP_ON_EVENT',CONVERT(sql_variant,@bitfalse),'bit',0,0,1,0), | |
(@execution_id,50,'String','DUMP_EVENT_CODE',CONVERT(sql_variant,'0'),'nvarchar',0,0,1,0), | |
(@execution_id,50,'Int32','LOGGING_LEVEL',CONVERT(sql_variant,CONVERT(INT,@server_logging_level)),'int',0,0,1,0), | |
(@execution_id,50,'String','CALLER_INFO',null,'nvarchar',0,0,1,0), | |
(@execution_id,50,'Boolean','SYNCHRONIZED',CONVERT(sql_variant,@bitfalse),'bit',0,0,1,0) | |
SET @sqlString = 'CLOSE SYMMETRIC KEY '+ @env_key_name | |
EXECUTE sp_executesql @sqlString | |
SET @sqlString = 'CLOSE SYMMETRIC KEY '+ @key_name | |
EXECUTE sp_executesql @sqlString | |
SET @sqlString = 'CLOSE SYMMETRIC KEY '+ @project_key_name | |
EXECUTE sp_executesql @sqlString | |
IF @tran_count = 0 | |
COMMIT TRANSACTION; | |
END TRY | |
BEGIN CATCH | |
IF @tran_count = 0 | |
ROLLBACK TRANSACTION; | |
ELSE IF XACT_STATE() <> -1 | |
ROLLBACK TRANSACTION @savepoint_name; | |
UPDATE [internal].[operations] SET | |
[end_time] = SYSDATETIMEOFFSET(), | |
[status] = 4 | |
WHERE operation_id = @operation_id; | |
THROW; | |
END CATCH | |
RETURN 0 | |
GO |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Still using this in SQL Server 2016? How did it work out for you?