December 7, 2020 at 2:00 am
I need a datetime stamp that once it's set won't change not matter how many times it's called by data flow tasks, because I want to use it to insert a a RunId value into my EventLog table to mark the start and end of a distinct package run.
I created a user variable at package level with below expression to capture datestamp to mm:ss:ms level and gave it string datatype so I can concatenate it to my package name.
Then I call it with ? in execute SQL Task
INSERT INTO [Log].EventLog
([Timestamp], RunID, EventType, [DataBase], [Schema], [Procedure], LoggedBy, EventMessage)
VALUES (GETDATE(), 'SSIS Package Name || ' +?, 'Start', etc)
INSERT INTO [Log].EventLog
([Timestamp], RunID, EventType, [DataBase], [Schema], [Procedure], LoggedBy, EventMessage)
VALUES (GETDATE(), 'SSIS Package Name || ' +?, 'Stop - Success', etc)
Entries for Start and End look like this, with reuse of variable on post execute giving a different time:
SSIS Package Name || 2020-12-07 00:59:31 Start
SSIS Package Name || 2020-12-07 01:08:03 Stop - Success
I need
SSIS Package Name || 2020-12-07 00:59:31 Start
SSIS Package Name || 2020-12-07 00:59:31 Stop - Success
I have tried using System::ContainerStartTime instead of my user defined variable, but it returns blank.
Please suggest how to get a datestamp that won't shift to time that it was called (and that I can concatenate to a string).
Thank you
--Quote me
December 7, 2020 at 9:09 am
Don't you want the "StartTime" parameter instead?
StartTime,DateTime,The time that the package started to run.
https://docs.microsoft.com/en-us/sql/integration-services/system-variables?view=sql-server-ver15#:~:text=System%20Variables%20for%20Packages%20%20%20%20System,was%20cr%20...%20%2018%20more%20rows%20
December 7, 2020 at 5:40 pm
Hi Ant-Green.
System::StartTime behaves just like System::ContainerStartTime in that it returns blank.
Not sure if it was clear before, but I use String datatype when setting this parameter in Parameter Mapping because I need to concatenate to a string as the receiving column is varchar(900) and it returns a blank (SSIS Package Name || Start). I have also tried DBTimestamp datatype with a convert to varchar(50), and that errors with datatype error.
INSERT INTO [Log].EventLog
([Timestamp], RunID, EventType, [DataBase], [Schema], [Procedure], LoggedBy, EventMessage)
VALUES (GETDATE(), 'SSIS Package Name || ' + convert(varchar(50),?) , etc)
Further suggestions are appreciated
--Quote me
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply