Skip to content

Instantly share code, notes, and snippets.

@kulmam92
Created July 6, 2013 14:03
Show Gist options
  • Save kulmam92/5939978 to your computer and use it in GitHub Desktop.
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/
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
@mattcargile
Copy link

Still using this in SQL Server 2016? How did it work out for you?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment