Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.