October 4, 2019 at 4:56 am
Hi,
I hope this is the right forum...
I have created some SSIS packages that a rather inexperienced end-user is responsible for running. She has limited SQL Server and SSMS skills. I have written explicit execution instructions, but that doesn't seem to be enough :-/
Those instructions:
My boss wants me to change this to a script that the end user can just open, edit as required, and run.
I've got that script working and have attached it to this post. It's a WIP but the functionality is there.
I want to convert this to a stored procedure with additional error checking.
This SP could be generic enough to run any package that has been deployed to Integration Services Catalogs. As such, I am thinking of creating the SP in the SSISDB, say spRunPackage. There would still be a script to execute, but the code would be encapsulated in the SP.
Is it a really bad idea to create this SP in SSISDB? Are there better approaches? What about an unscheduled job? But as far as I can tell, there's no way to dynamically set parameters of a job at execution time.
Thanks...
Edit: Bizarre that I can't attached a .sql file on this site!
October 4, 2019 at 7:51 am
I've got that script working and have attached it to this post.
Actually you didn't attach it, but don't. You'll be better off putting the relevant code in a reply in the code sample box.
To answer the question of "Is it a bad idea to create a user-defined stored procedure in SSISDB database?" personally i try to avoid putting functions in "System" databases (it's not a real system database), but if I had to, I would put it in a different schema so i know that they aren't part of the standard install, and ensure that they are scripted out appropriately. But for something like this, I would personally put it in a "normal" user database, with cross database calls to the appropriate SSISDB SPs.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 4, 2019 at 8:58 am
Thanks Thom.
I've edited my OP to attach the SP.
I've finished the SP so include it here for suggested improvements.
I've written it to SSISDB.dbo.spRunPackage. Since there are no dbo.* SP's in that database, I'm hoping that's OK. Unfortunately I can't convince my "DBA" to create a generic utility DB for generic SP's, functions, views, etc.
USE [SSISDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*=====================================================================
Stored Procedure Name : spRunPackage
Purpose : Programmatically execute an SSIS package.
SQL Server Version : Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64)
Other Code Called : None
Originally Written by :
Date : 04OCT2019
Stored Process Version : 1.0
=======================================================================
Modification History : Original version
=====================================================================*/
/*---------------------------------------------------------------------
Usage:
EXEC [SSISDB].[dbo].[spRunPackage] @folder_name = 'TESTING'
,@project_name = 'Test1'
,@environment_name = 'Dev'
,@package_name = 'Test spRunPackage.dtsx'
-- OR
EXEC [SSISDB].[dbo].[spRunPackage] @folder_name = 'TESTING'
,@project_name = 'Test1'
,@environment_name = 'Dev'
,@package_name = 'Test spRunPackage.dtsx'
,@synchronized = 0
,@run = 0
Completes all error checking for the supplied parameters,
but does not execute the package.
Run=0 (error checking only) is the default.
=======================================================================
EXEC [SSISDB].[dbo].[spRunPackage] @folder_name = 'TESTING'
,@project_name = 'Test1'
,@environment_name = 'Dev'
,@package_name = 'Test spRunPackage.dtsx'
,@synchronized = 0
,@run = 1
-- This will run immediately even though the package is still running
SELECT 'Package is still running' AS Message
Completes all error checking for the supplied parameters
and runs the package asynchronously (nowait),
since run=1 and synchronized=0.
Asynchronous (nowait) processing is the default.
=======================================================================
EXEC [SSISDB].[dbo].[spRunPackage] @folder_name = 'TESTING'
,@project_name = 'Test1'
,@environment_name = 'Dev'
,@package_name = 'Test spRunPackage.dtsx'
,@synchronized = 1
,@run = 1
-- This will not run until the package is completed
SELECT 'Package has completed running' AS Message
Completes all error checking for the supplied parameters
and runs the package synchronously (wait),
since run=1 and synchronized=1.
This will suspend all downstream processing until the package completes.
=======================================================================
EXEC [SSISDB].[dbo].[spRunPackage] @rldx_audit_key = 44
,@folder_name = 'Flatten'
,@project_name = 'Deaths'
,@environment_name = 'Prod%'
,@package_name = 'ext.RLDX_RECORD.dtsx'
,@synchronized = 1
,@run = 0
-- Then
EXEC [SSISDB].[dbo].[spRunPackage] @rldx_audit_key = 44
,@folder_name = 'Flatten'
,@project_name = 'Deaths'
,@environment_name = 'Prod%'
,@package_name = 'ext.RLDX_RECORD.dtsx'
,@synchronized = 1
,@run = 1
-- This will not run until the package is completed
-- For example, this could send an email when the package is completed
SELECT 'Package has completed running' AS Message
Completes all error checking for the supplied parameters
and runs the package synchronously (wait),
since run=1 and synchronized=1.
Since this project contains the RLDX_AUDIT_KEY parameter,
the desired value of RLDX_AUDIT_KEY must be specified as a parameter
to this stored procedure or an error will be thrown.
Wildcards can be used for the environment name since the lookup uses
the LIKE operator.
=======================================================================
Error Checking:
EXEC [SSISDB].[dbo].[spRunPackage]
EXEC [SSISDB].[dbo].[spRunPackage] @folder_name = 'Flatten'
EXEC [SSISDB].[dbo].[spRunPackage] @folder_name = 'Flatten'
,@project_name = 'Deaths'
EXEC [SSISDB].[dbo].[spRunPackage] @folder_name = 'Flatten'
,@project_name = 'Deaths'
,@environment_name = 'Prod%'
Required parameters were not specified.
EXEC [SSISDB].[dbo].[spRunPackage] @folder_name = 'Flatten'
,@project_name = 'Deaths'
,@environment_name = 'Prod%'
,@package_name = 'ext.RLDX_RECORD'
Package was not found in this project (.dtsx extension is required).
EXEC [SSISDB].[dbo].[spRunPackage] @folder_name = 'Flatten'
,@project_name = 'Deaths'
,@environment_name = 'XXX'
,@package_name = 'ext.RLDX_RECORD.dtsx'
Environment not found.
EXEC [SSISDB].[dbo].[spRunPackage] @folder_name = 'Flatten'
,@project_name = 'Deaths'
,@environment_name = 'Prod%'
,@package_name = 'ext.RLDX_RECORD.dtsx'
The project contains the RLDX_AUDIT_KEY parameter but the desired value
was not specified as a parameter to the stored procedure.
-----------------------------------------------------------------------
Notes:
If you're not sure whether your parameters are correct, specify run=0.
The parameters will be error checked and displayed in the Results window.
The SSISDB is a case-insensitive database.
Therefore folder_name, project_name, environment_name, etc.
are case-insensitive lookups.
---------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[spRunPackage]
(
@rldx_audit_key INT = NULL
,@folder_name SYSNAME
,@project_name SYSNAME
,@environment_name SYSNAME
,@package_name SYSNAME
,@synchronized BIT = 0 -- 0=asynchronously (nowait), 1=synchronously (wait)
,@run BIT = 0 -- 0=display parameters only, 1=run package
)
AS
BEGIN
DECLARE @folder_id BIGINT
,@project_id BIGINT
,@reference_id BIGINT
,@parameter_id BIGINT = 0
;
-- Get Folder ID
SELECT @folder_id = f.folder_id
FROM [SSISDB].[catalog].[folders] f
WHERE f.name = @folder_name
IF @@rowcount != 1
BEGIN
RAISERROR ('Folder %s was not found.',16,1,@folder_name);
RETURN
END
-- Get Project ID
SELECT @project_id = p.project_id
FROM [SSISDB].[catalog].[projects] p
WHERE p.name = @project_name
IF @@rowcount != 1
BEGIN
RAISERROR ('Project %s was not found.',16,1,@project_name);
RETURN
END
-- Get Environment Reference
SELECT @reference_id = e.reference_id
FROM [SSISDB].[catalog].[environment_references] e
WHERE e.project_id = @project_id
AND e.environment_name LIKE @environment_name
IF @@rowcount != 1
BEGIN
RAISERROR ('Environment %s was not found.',16,1,@environment_name);
RETURN
END
-- Does the Package exist?
IF NOT EXISTS (
SELECT 1
FROM [SSISDB].[catalog].[packages]
WHERE project_id = @project_id
AND name = @package_name
)
BEGIN
RAISERROR ('Package %s was not found in project %s.',16,1,@package_name,@project_name);
RETURN
END
-- Get Parameter ID for RLDX_AUDIT_KEY parameter
-- (if it is in this Project)
SELECT @parameter_id = op.parameter_id
FROM [SSISDB].[catalog].[object_parameters] op
WHERE op.project_id = @project_id
AND op.parameter_name = 'RLDX_AUDIT_KEY'
-- If the project contains the RLDX_AUDIT_KEY parameter
-- it must be specified as a parameter to this stored procedure
IF @parameter_id != 0
BEGIN
IF @rldx_audit_key IS NULL
BEGIN
RAISERROR ('RLDX_AUDIT_KEY must be specified for the %s project.',16,1,@project_name);
RETURN;
END
-- Set the RLDX_AUDIT_KEY parameter for the project
DECLARE @rc INT;
EXEC @rc = CATALOG.set_object_parameter_value @object_type = 20
,@folder_name = @folder_name
,@project_name = @project_name
,@parameter_name = 'RLDX_AUDIT_KEY'
,@parameter_value = @rldx_audit_key
,@object_name = @folder_name
,@value_type = 'V'
END
-- Display the results
SELECT f.folder_id
,p.project_id
,e.reference_id
,f.name AS folder_name
,p.name AS project_name
,e.environment_name
FROM [SSISDB].[catalog].[environment_references] e
JOIN [SSISDB].[catalog].[projects] p
ON p.project_id = e.project_id
JOIN [SSISDB].[catalog].[folders] f
ON p.folder_id = f.folder_id
WHERE e.reference_id = @reference_id
-- If RLDX_AUDIT_KEY is in this project display its value
IF @parameter_id != 0
BEGIN
SELECT IIF(@rc = 0,'SUCCESS','FAILURE') AS results
,op.project_id
,f.name AS folder_name
,op.object_name AS project_name
,op.parameter_name
,op.default_value
FROM [SSISDB].[catalog].[object_parameters] op
JOIN [SSISDB].[catalog].[projects] p
ON op.project_id = p.project_id
JOIN [SSISDB].[catalog].[folders] f
ON p.folder_id = f.folder_id
WHERE op.project_id = @project_id
AND op.parameter_name = 'RLDX_AUDIT_KEY'
IF @rc != 0
BEGIN
RAISERROR ('Error setting the RLDX_AUDIT_KEY value to %s.',16,1,@rldx_audit_key)
RETURN;
END
END
-- Run the Package
IF @run = 1
BEGIN
DECLARE @execution_id BIGINT
EXEC [SSISDB].[catalog].[create_execution] @folder_name = @folder_name
,@project_name = @project_name
,@package_name = @package_name
,@reference_id = @reference_id
,@use32bitruntime = FALSE
,@execution_id = @execution_id OUTPUT
-- create_execution will throw an error if there is a problem
-- so no further error processing is needed
SELECT @execution_id AS execution_id
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id
,@object_type = 50
,@parameter_name = N'SYNCHRONIZED'
,@parameter_value = @synchronized;
EXEC [SSISDB].[catalog].[start_execution] @execution_id
END
END
GO
I wish I could get the Results to display immediately when I'm doing synchronous processing, but specifying "GO" after display results is problematic. And minor, so I'll live with it.
October 4, 2019 at 1:56 pm
Shame about your DBA. A Utility database is a rather good idea, in my opinion – I'm with Thom about not putting non-system objects inside system (or pseudo-system) databases. With the very occasional exception on the master database, where sometime it's useful to create a 'system' stored proc. Having non-system objects in a system DB can also make CI/CD more difficult to manage.
If the proc is deployed to SSISDB, I'd also suggest removing all of the [SSISDB] object qualifiers in your proc, for the sake of good practice.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 4, 2019 at 2:04 pm
We have a dumping ground Utility Database here and it works well.If you can try and get him to come around to the idea, you'll likely start finding other uses for it quickly (such as perhaps a location for Jeff, and Eirikur's, functions DelimitedSplit8K_LEAD or Alan's NGrams8K).
Phil is right though, as they are all internal references, you should remove the 3 part naming an just use 2 part.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 4, 2019 at 2:26 pm
We have a
dumping groundUtility Database here and it works well.If you can try and get him to come around to the idea, you'll likely start finding other uses for it quickly (such as perhaps a location for Jeff, and Eirikur's, functionsDelimitedSplit8K_LEADor Alan'sNGrams8K).Phil is right though, as they are all internal references, you should remove the 3 part naming an just use 2 part.
I'd also dump Regex CLRs in there!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply