Is it a bad idea to create a user-defined stored procedure in SSISDB database?

  • 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:

    • From Integration Services Catalogs -> SSISDB -> Folder Name -> Projects -> Project Name, RMB, Configure
    • Set a particular Project Level parameter (RLDX_AUDIT_KEY).  This parameter is the keystone for the rest of the downstream packages.
    • RMB the main wrapper package (contains Execute Package tasks in a particular workflow) -> Execute -> Check Environment -> Select .\Production from the dropdown, and click OK.

    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!

    • This topic was modified 5 years, 2 months ago by  Scott In Sydney. Reason: Attaching a .sql file fails for security reasons
    Attachments:
    You must be logged in to view attached files.
  • Scott In Sydney wrote:

    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

  • 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.

     

  • 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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • 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

  • Thom A wrote:

    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.

    I'd also dump Regex CLRs in there!

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply