I created a very simple table in Data Mart to store ETL diagnostic information:
CREATE TABLE [internal].[etl_status] ( [property_name] VARCHAR(100) NOT NULL PRIMARY KEY, [property_value] VARCHAR(MAX) NULL )
Then I created a stored procedure to handle insertions/modifies into this table. Besides convenience, I had to do this because Microsoft are yet to add T-SQL statement activities to Azure data factory.
Problem is, I can't figure how to make the store procedure accept T-SQL function as parameters. In fact I believe it might not be possible. So here is my little workaround:
CREATE PROCEDURE [internal].[sp_set_etl_status] @propertyName VARCHAR(100) ,@propertyValue VARCHAR(MAX) AS DECLARE @query AS VARCHAR(MAX) -- Case statement to handle functions passed as propertyValue parameter -- NOT IDEAL, see solution by Scott Hodgin below SET @propertyValue = CASE @propertyValue WHEN 'GETDATE()' THEN CONVERT(VARCHAR(MAX),CONVERT(DATETIME2(2), GETUTCDATE())) ELSE @propertyValue END SET @query = ' BEGIN IF NOT EXISTS ( SELECT * FROM [internal].[etl_status] WHERE [property_name] =''' + @propertyName + ''') BEGIN INSERT INTO [internal].[etl_status] VALUES (''' + @propertyName + ''', NULL) END END UPDATE [internal].[etl_status] SET [property_value] = ''' + @propertyValue+ ''' WHERE [property_name] = ''' + @propertyName + ''' ;' EXEC(@query)
This works as expected. But with the current solution I would need to have a CASE statement for each function I would like to handle.
EDIT: The suggestion below by Scott Hodgin is an acceptable solution. However I added a default value of
@propertyValueIsFunction as follows:
@propertyValueIsFunction BIT = FALSE.