SSIS Nuggets

  -- -- 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 -- -- 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'MA_CONTRACT_DATA.dtsx' , @TARGET_NAME NVARCHAR(256) = N'H9999'; ;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 +   '( ' + ...

 -- -- 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' , @...

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