SSIS Deployment and Configuration Presentation

 Demo for using package parameters; shows how to store package parameters and use them at runtime.  Execute an SSIS package and select the set of package parameters to use.


--
-- SCRIPT: REPORT_AUTOMATION_DEMO
--
USE ReportAutomation
GO


--
-- Calendar table
--
CREATE TABLE [dbo].[Calendar]
(
[Id] INT IDENTITY NOT NULL PRIMARY KEY,
[CalendarDate] DATE NOT NULL,
[CalendarYear] INT NOT NULL,
[CalendarQuarter] INT NOT NULL,
[CalendarQuarterLiteral] VARCHAR(10) NOT NULL
)
GO

--
-- Stored procedure to populate Calendar table
--
/*

STORED PROCEDURE: CreateCalendar

Inserts the range of rows specified by the stored procedure parameters.

NOTE: Only inserts if the rows don't already exist.

EXEC [dbo].[CreateCalendar] @StartDate='20160101', @EndDate='20171231'

TO DO: Must set MAXRECURSION if creating more than 10 years

*/
CREATE PROCEDURE [dbo].[CreateCalendar]
@StartDate DATE,
@EndDate DATE
AS
BEGIN

;WITH CTE AS (
SELECT 
  @StartDate CalendarDate

UNION ALL

SELECT 
  DATEADD(day, 1, CalendarDate)
FROM CTE
WHERE CalendarDate < @EndDate
)
,
CTE_2 AS (
-- only add new calendar dates that aren't already in the Calendar table
SELECT CalendarDate
FROM CTE

EXCEPT

SELECT CalendarDate
FROM dbo.Calendar
)
,
CTE_3 AS (
SELECT
  CalendarDate
, DATEPART(year, CalendarDate) CalendarYear
, DATEPART(quarter, CalendarDate) CalendarQuarter
FROM CTE_2
)

INSERT dbo.Calendar (
[CalendarDate]
, [CalendarYear]
, [CalendarQuarter]
, [CalendarQuarterLiteral]
)
SELECT 
CalendarDate
, CalendarYear
,   CalendarQuarter
,  CASE CalendarQuarter
WHEN 1 THEN 'JAN-MAR'
WHEN 2 THEN 'APR-JUN'
WHEN 3 THEN 'JUL-SEP'
WHEN 4 THEN 'OCT-DEC'
END
FROM CTE_3
OPTION (MAXRECURSION 3660)

END
GO


--
-- Table to store package parameters
--
CREATE TABLE [dbo].[TargetSSISPackageParameter]
(
  [Id] INT IDENTITY NOT NULL 
CONSTRAINT TargetSSISPackageParameter_PK PRIMARY KEY
, [TargetName] NVARCHAR(50) NOT NULL -- "who" the package is being run for
, [SSISPackageName] NVARCHAR(50) NOT NULL -- name of SSIS package to run
, [ParameterName] NVARCHAR(50) NOT NULL -- required package parameter (per the catalog)
, [ParameterValue] NVARCHAR(256) NOT NULL -- static value or variable replaced at runtime
, [LastUpdated] DATETIME NOT NULL
)
GO


--
-- Show the required package parameters for a package deployed to the catalog
--
SELECT * FROM SSISDB.catalog.object_parameters
WHERE object_name = N'CLAIM_LAG.dtsx'
AND required=1
AND object_type=30; -- package parameter
GO


--
-- Generate a TVC that we can use to load the parameter values into the TargetSSISPackageParameter table
-- SCRIPT: GENERATE_PACKAGE_PARAMETER_SETTINGS
-- ==> SET QUERY RESULTS TO TEXT <==
--
DECLARE 
  @SSIS_PACKAGE_NAME NVARCHAR(256) = N'CLAIM_LAG.dtsx'
, @TARGET_NAME NVARCHAR(256) = N'AMERIGROUP';

;WITH CTE AS (
SELECT
   ROW_NUMBER() OVER (ORDER BY (SELECT 1)) ROW_NUM
,  [object_name]
,  parameter_name
FROM SSISDB.catalog.object_parameters
WHERE object_name = @SSIS_PACKAGE_NAME
AND required = 1
AND [object_type] = 30 -- package parameter
)

SELECT
  CASE WHEN ROW_NUM > 1 THEN ',' ELSE ' ' END +
  '( ' + 
  '''' + @TARGET_NAME + '''' + 
  ', ' + '''' + [object_name] + '''' +
  ', ' + '''' + parameter_name + '''' +
  ', ' + '''' + 'PARAMETER_VALUE_GOES_HERE' + '''' +
  ')'
FROM CTE

/* OUTPUT:

 ( 'AMERIGROUP', 'CLAIM_LAG.dtsx', 'END_DATE', 'PARAMETER_VALUE_GOES_HERE')
,( 'AMERIGROUP', 'CLAIM_LAG.dtsx', 'FlatFileConnectionManager_ConnectionString', 'PARAMETER_VALUE_GOES_HERE')
,( 'AMERIGROUP', 'CLAIM_LAG.dtsx', 'START_DATE', 'PARAMETER_VALUE_GOES_HERE')

*/


--
-- Load package settings for TargetName
-- SCRIPT: MERGE_PACKAGE_PARAMETER_SETTINGS
--
DECLARE @LAST_UPDATED DATETIME = GETDATE();

    ;WITH cte_source AS (
SELECT src.TargetName, src.SSISPackageName, src.ParameterName, src.ParameterValue 
FROM (VALUES
-- INSERT TVF from GENERATE_PACKAGE_PARAMETER_SETTINGS script

 ( 'AMERIGROUP', 'CLAIM_LAG.dtsx', 'END_DATE', '[DefaultMonthEndDate]')
,( 'AMERIGROUP', 'CLAIM_LAG.dtsx', 'FlatFileConnectionManager_ConnectionString', '\\BALHSPDB04\BALHSPDB04_Test\CLAIMS_LAG.CSV')
,( 'AMERIGROUP', 'CLAIM_LAG.dtsx', 'START_DATE', '[DefaultMonthBeginDate]')
-- 
) 
AS src(TargetName, SSISPackageName, ParameterName, ParameterValue)
    )

    MERGE dbo.TargetSSISPackageParameter AS tgt

    USING (SELECT * FROM cte_source) AS src
ON (
    tgt.TargetName = src.TargetName 
AND tgt.SSISPackageName = src.SSISPackageName 
AND tgt.ParameterName = src.ParameterName
)

    WHEN MATCHED THEN
    UPDATE SET
              ParameterValue=src.ParameterValue
, LastUpdated=@LAST_UPDATED

    WHEN NOT MATCHED THEN
            INSERT (
TargetName
  , SSISPackageName
  , ParameterName
  , ParameterValue
  , [LastUpdated]
)
            VALUES (
src.TargetName
  , src.SSISPackageName
  , src.ParameterName
  , src.ParameterValue
  , @LAST_UPDATED
);


--
-- Show package parameter settings for TargetName and SSISPackageName
--
SELECT * 
FROM dbo.TargetSSISPackageParameter
WHERE TargetName=N'AMERIGROUP'
AND SSISPackageName=N'CLAIM_LAG.dtsx';


--
-- Show default date ranges based on @REPORT_DATE
--
DECLARE 
  @REPORT_DATE DATE = GETDATE();

SELECT
  *
FROM dbo.GetDefaultDatesForReport(@REPORT_DATE);


--
-- GetDefaultDatesForReport TVF
--
/*

FUNCTION: [dbo].[GetDefaultDatesForReport]

Returns default dates for reporting based on the @RUN_DATE parameter which would normally be
the current date but could be any date.

MonthBeginDate and MonthEndDate are for the month previous to @REPORT_DATE

DECLARE @REPORT_DATE DATE = GETDATE();
SELECT * FROM [dbo].[GetDefaultDatesForReport](@REPORT_DATE);

*/
CREATE FUNCTION [dbo].[GetDefaultDatesForReport]
(
@REPORT_DATE DATE
)
RETURNS @returntable TABLE
(
  [ParameterName] VARCHAR(50)
, [ParameterValue] DATE
)
AS
BEGIN
DECLARE
  @MONTH_BEGIN_DATE DATE
, @MONTH_END_DATE DATE
, @QUARTER_BEGIN_DATE DATE
, @QUARTER_END_DATE DATE
, @QUARTER INT = DATEPART(quarter, @REPORT_DATE)
, @QUARTER_YEAR INT = DATEPART(year, @REPORT_DATE)
, @YEAR INT = DATEPART(year, @REPORT_DATE)
, @YEAR_YEAR INT
, @YEAR_BEGIN_DATE DATE
, @YEAR_END_DATE DATE;

-- calculate monthly report date range
SET @MONTH_END_DATE = EOMONTH(@REPORT_DATE, -1);
SET @MONTH_BEGIN_DATE = DATEADD(dd, 1, EOMONTH(@MONTH_END_DATE, -1));

-- calculate quarterly report date range
IF @QUARTER = 1  
BEGIN
SET @QUARTER_YEAR-=1;
SET @QUARTER=4;
END
ELSE
BEGIN
SET @QUARTER-=1;
END;

SELECT 
  @QUARTER_BEGIN_DATE=MIN(CalendarDate)
, @QUARTER_END_DATE=MAX(CalendarDate)
FROM dbo.Calendar
WHERE [CalendarYear]=@QUARTER_YEAR
AND [CalendarQuarter]=@QUARTER;

-- calculate yearly report date range
SET @YEAR_YEAR = @YEAR - 1;
SELECT 
  @YEAR_BEGIN_DATE=MIN(CalendarDate)
, @YEAR_END_DATE=MAX(CalendarDate)
FROM dbo.Calendar
WHERE [CalendarYear]=@YEAR_YEAR;

INSERT @returntable(
  [ParameterName]
, [ParameterValue]
)
VALUES
  ('[DefaultMonthBeginDate]', @MONTH_BEGIN_DATE)
, ('[DefaultMonthEndDate]', @MONTH_END_DATE)
, ('[DefaultQuarterBeginDate]', @QUARTER_BEGIN_DATE)
, ('[DefaultQuarterEndDate]', @QUARTER_END_DATE)
, ('[DefaultYearBeginDate]', @YEAR_BEGIN_DATE)
, ('[DefaultYearEndDate]', @YEAR_END_DATE);

RETURN
END
GO


--
-- Retrieve package parameter values for running an SSIS package for TargetName
-- SCRIPT: GET_PACKAGE_PARAMETER_VALUES
--
DECLARE 
  @REPORT_DATE DATE = GETDATE()
, @TARGET_NAME NVARCHAR(50) = N'AMERIGROUP'
, @SSIS_PACKAGE_NAME NVARCHAR(50) = N'CLAIM_LAG.dtsx';

-- using CTE because there may be more defaults than just dates
;WITH CTE_PARAMETER_DEFAULT AS (
SELECT
  ParameterName
, ParameterValue
, CONVERT(SQL_VARIANT, ParameterValue) ParameterValueAsVariant
FROM [dbo].[GetDefaultDatesForReport](@REPORT_DATE)
)
,
CTE_PACKAGE_PARAMETER AS (
SELECT
  p.ParameterName
, CASE WHEN d.ParameterValue IS NOT NULL
THEN ParameterValueAsVariant
ELSE CONVERT(SQL_VARIANT, p.ParameterValue) END ParameterValue
, d.ParameterValue DefaultParameterValue
FROM dbo.TargetSSISPackageParameter p
LEFT JOIN CTE_PARAMETER_DEFAULT d ON d.ParameterName = p.ParameterValue
WHERE p.TargetName = @TARGET_NAME
AND p.SSISPackageName = @SSIS_PACKAGE_NAME
)

SELECT *, SQL_VARIANT_PROPERTY(ParameterValue, 'BaseType') 
FROM CTE_PACKAGE_PARAMETER


--
-- TEST CASES
--

-- (1) Execute SSIS package (no parameter overrides)

EXEC [dbo].[ExecuteAutomatedSSISPackage]
@TARGET_NAME = N'AMERIGROUP'
, @SSIS_PACKAGE_NAME = N'CLAIM_LAG.dtsx'
, @ENVIRONMENT_NAME = N'DEV';


--(2) Execute SSIS package; override FlatFileConnectionManager_ConnectionString 

DECLARE 
  @CUSTOM_PARAMETER_TABLE dbo.ReportParameterType;

INSERT @CUSTOM_PARAMETER_TABLE([Name], [Value])
SELECT p.[Name], p.[Value]
FROM (
VALUES 
(N'FlatFileConnectionManager_ConnectionString', N'\\BALHSPDB04\BALHSPDB04_Test\CLAIM_LAG.CSV')
) AS p([Name], [Value]);

EXEC [dbo].[ExecuteAutomatedSSISPackage]
@TARGET_NAME = N'AMERIGROUP'
, @SSIS_PACKAGE_NAME = N'CLAIM_LAG.dtsx'
, @ENVIRONMENT_NAME = N'DEV'
, @CUSTOM_PARAMETER_TABLE = @CUSTOM_PARAMETER_TABLE;


-- (3) Execute SSIS package; override START_DATE and END_DATE 

DECLARE 
  @CUSTOM_PARAMETER_TABLE dbo.ReportParameterType;

INSERT @CUSTOM_PARAMETER_TABLE([Name], [Value])
SELECT p.[Name], p.[Value]
FROM (
VALUES 
(N'START_DATE', CONVERT(DATE, N'20170101'))
, (N'END_DATE',   CONVERT(DATE, N'20170131'))
) AS p([Name], [Value]);

EXEC [dbo].[ExecuteAutomatedSSISPackage]
@TARGET_NAME = N'AMERIGROUP'
, @SSIS_PACKAGE_NAME = N'CLAIM_LAG.dtsx'
, @ENVIRONMENT_NAME = N'DEV'
, @CUSTOM_PARAMETER_TABLE = @CUSTOM_PARAMETER_TABLE;


-- (4) Execute SSIS package; add parameters NOT in [TargetSSISPackageParameter] table

DECLARE 
  @CUSTOM_PARAMETER_TABLE dbo.ReportParameterType;

INSERT @CUSTOM_PARAMETER_TABLE([Name], [Value])
SELECT p.[Name], p.[Value]
FROM (
VALUES 
(N'NEW_PARAMETER_NAME', N'NEW PARAMETER VALUE')
) AS p([Name], [Value]);

EXEC [dbo].[ExecuteAutomatedSSISPackage]
@TARGET_NAME = N'AMERIGROUP'
, @SSIS_PACKAGE_NAME = N'CLAIM_LAG.dtsx'
, @ENVIRONMENT_NAME = N'DEV'
, @CUSTOM_PARAMETER_TABLE = @CUSTOM_PARAMETER_TABLE;


Comments

Post a Comment

Popular posts from this blog

Execute SSIS Package - Specify All Required package parameters

 -- -- Show the required package parameters for a package deployed to the catalog -- SELECT * FROM SSISDB.catalog.object_ parameters WHERE object_name = N'MA_CONTRACT_DATA.dtsx' AND required=1 AND object_type=30; -- package parameter GO -- execute the package; specify all required package parameters DECLARE    @CUSTOM_PARAMETER_TABLE dbo.ReportParameterType; INSERT @CUSTOM_PARAMETER_TABLE([Name] , [Value]) SELECT p.[Name], p.[Value] FROM ( VALUES    (N'CONTRACT_NUMBER', CONVERT(SQL_VARIANT, N'H9999')) , (N'START_DATE', CONVERT(SQL_VARIANT, CONVERT(DATE, N'20160101'))) , (N'END_DATE',   CONVERT(SQL_VARIANT, CONVERT(DATE, N'20161231'))) , (N'OUTPUT_UNC_PATH', CONVERT(SQL_VARIANT, N'\\OUTBOUND')) ) AS p([Name], [Value]); EXEC [dbo].[ ExecuteAutomatedSSISPackage] @TARGET_NAME = N'CUSTOM' , @SSIS_PACKAGE_NAME = N'MA_CONTRACT_DATA.dtsx' , @...
Read more

Get Selected Project Parameters for a Project from the SSIS Catalog

 USE SSISDB GO -- I'm planning on leaving the DEV values in the SSIS package and updating after deployment  -- to the catalog DECLARE  @FOLDER_NAME NVARCHAR(128) = N'< folder name goes here >' , @PROJECT_NAME NVARCHAR(128) = N'< project name goes here >'; /* get the parameters for a project object_type 20 project parameter 30 package parameter object_name when object_type = 20, project name when object_type = 30, SSIS package name */ SELECT o.* FROM [catalog].[folders] f JOIN [catalog].[projects] p ON p.folder_id = f.folder_id JOIN [catalog].[object_parameters] o ON o.[project_id] = p.[project_id] WHERE f.[name] = @FOLDER_NAME AND p.[name] = @PROJECT_NAME -- project parameters AND o.object_type = 20 AND ( -- just get specific parameter names o.parameter_name LIKE N'%.ConnectionString' OR o.parameter_name LIKE N'%.ServerName'  ) ;
Read more