SSIS Nuggets

 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'  ) ;

Call a stored procedure in the Execute SQL Task and it returns OUT parameters. For a DATE OUT parameter, use DBDATE as the Data Type for the SSIS DateTime variable. For a DATETIME OUT parameter, use DBTIME as the Data Type for the SSIS DateTime variable. Execute a T-SQL INSERT in the Execute SQL Task that takes a DATETIME parameter in the VALUES clause, use DATE as the Data Type for the SSIS DateTime variable.

https://www.mssqltips.com/sqlservertip/6481/install-sql-server-integration-services-in-visual-studio-2019/  

https://www.mssqltips.com/sqlservertip/6172/execute-powershell-script-from-ssis-package/  

 CREATE PROCEDURE [dbo].[CREATE_EntitySelectionExecution] @P_PROCEDURE_NAME NVARCHAR(128) , @P_STAGE_ID INT , @P_BRANCH_NUMBER INT , @P_CLOSE_DATE DATETIME = NULL , @P_FILTER_DATE DATETIME = NULL , @P_START_EXECUTION INT = 1 , @P_EXECUTION_ID BIGINT OUT AS BEGIN /* Call the [SSISDB].[catalog].[create_execution] stored procedure for one of the SSIS packages in the <FILL IN> project. */ DECLARE @EXECUTION_ID BIGINT , @FOLDER_NAME NVARCHAR(128) , @PROJECT_NAME NVARCHAR(128) , @PACKAGE_NAME NVARCHAR(128) , @ENVIRONMENT_REFERENCE_ID BIGINT; -- Lookup the mapping for the calling stored procedure SELECT @FOLDER_NAME = [FolderName] , @PROJECT_NAME = [ProjectName] , @PACKAGE_NAME = [PackageName] , @ENVIRONMENT_REFERENCE_ID = [EnvironmentReferenceID] FROM [dbo].[MAP_ProcedureToPackage] WHERE [ProcedureName] = @P_PROCEDURE_NAME; IF @@ROWCOUNT = 0 THROW 2147483647, N...

  https://www.sqlshack.com/ssis-and-powershell-execute-process-task/