Execute SQL Task, OLEDB Connection and Parameters

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.



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