WordPress.com



USE [SSISDB]GO/****** Object: StoredProcedure [catalog].[create_execution_new] Script Date: 6/26/2013 3:14:41 PM ******/SET ANSI_NULLS OFFGOSET QUOTED_IDENTIFIER OFFGO/*Declare @execution_id bigintEXEC [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=1Select @execution_idDeclare @execution_id bigintEXEC [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=1Select @execution_idexec sp_executesql N'DECLARE @var0 smallint = 1EXEC [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=10009goexec sp_executesql N'EXEC [SSISDB].[catalog].[start_execution] @execution_id',N'@execution_id bigint',@execution_id=10009go*/ALTER 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 outputWITH EXECUTE AS 'AllSchemaOwner'ASSET NOCOUNT ONDECLARE @caller_id intDECLARE @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 datetimeoffsetDECLARE @return_value intDECLARE @operation_id bigintDECLARE @result bitDECLARE @environment_found bitDECLARE@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 INTDECLARE @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 BEGINRAISERROR(27156, 16, 1, 'ENCRYPTION_ALGORITHM') WITH NOWAITENDEXECUTE AS CALLEREXEC [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_principalsWHERE [sid] = @suser_sid AND [type] = 'S') OREXISTS(SELECT [name]FROM sys.database_principalsWHERE ([sid] = @caller_sid AND [type] = 'S')) )BEGINRAISERROR(27123, 16, 1) WITH NOWAITRETURN 1ENDREVERT IF(EXISTS(SELECT [name]FROM sys.server_principalsWHERE [sid] = @suser_sid AND [type] = 'S') OREXISTS(SELECT [name]FROM sys.database_principalsWHERE ([sid] = @caller_sid AND [type] = 'S')) )BEGINRAISERROR(27123, 16, 1) WITH NOWAITRETURN 1ENDIF (@folder_name IS NULL OR @project_name IS NULL OR @package_name IS NULL OR @use32bitruntime IS NULL)BEGINRAISERROR(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 <> 0RETURN 1;EXECUTE AS CALLEREXEC @return_value = [internal].[init_object_permissions] 4, @operation_id, @caller_idREVERT IF @return_value <> 0 BEGIN RAISERROR(27153, 16, 1) WITH NOWAITRETURN 1END SET @execution_id = @operation_idEND TRYBEGIN CATCHUPDATE [internal].[operations] SET [end_time] = SYSDATETIME() , [status] = 4 WHERE operation_id = @operation_id;THROW;END CATCH SET TRANSACTION ISOLATION LEVEL SERIALIZABLESELECT @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]= 20IF (@project_id IS NULL) BEGINRAISERROR(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) BEGINRAISERROR(27208, 16, 1, @reference_id) WITH NOWAITENDSET @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 BEGINSET @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] = 0then 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' thencase when params.sensitive=0 then params.[default_value] end elsecase when vars.sensitive=0 then vars.[value] end end [parameter_value], case when params.[value_type] = 'V' thencase when params.sensitive=1 then ENCRYPTBYKEY(KEY_GUID(@key_name), DECRYPTBYKEY(sensitive_default_value)) end elsecase 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 27148when 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] refson refs.reference_id=@reference_id and params.[project_id]=refs.[project_id] left JOIN [internal].[environment_variables] varsON 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] = 20OR (params.[object_name] = @package_name AND params.[object_type] = 30)) AND params.[project_version_lsn] = @version_id ) paramsselect @return_value=max(err) from @execution_parameter_valuesif @return_value<>0 beginRAISERROR(@return_value , 16 , 1) WITH NOWAITendSET TRANSACTION ISOLATION LEVEL READ COMMITTEDset @tran_count = @@TRANCOUNT;IF @tran_count > 0 BEGINSET @savepoint_name = REPLACE(CONVERT(NCHAR(36), NEWID()), N'-', N'');SAVE TRANSACTION @savepoint_name;ENDELSEBEGIN 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--ENDUPDATE [internal].[operations]SET [object_id] = @project_idWHERE [operation_id] = @operation_idIF @@ROWCOUNT <> 1 BEGINRAISERROR(27112, 16, 1, N'operations') WITH NOWAITEND 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_nameEXECUTE sp_executesql @sqlStringSET @sqlString = 'CLOSE SYMMETRIC KEY '+ @key_nameEXECUTE sp_executesql @sqlString SET @sqlString = 'CLOSE SYMMETRIC KEY '+ @project_key_nameEXECUTE sp_executesql @sqlString IF @tran_count = 0COMMIT TRANSACTION; END TRY BEGIN CATCH IF @tran_count = 0 ROLLBACK TRANSACTION; ELSE IF XACT_STATE() <> -1ROLLBACK TRANSACTION @savepoint_name; UPDATE [internal].[operations] SET [end_time] = SYSDATETIMEOFFSET(),[status] = 4WHERE operation_id = @operation_id;THROW;END CATCH RETURN 0GO ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download