Tag Archives: BIML

My Favourite SSIS Log Setup

After playing around with BIML for the last week, I have finally found a Log Setup for SSIS that I really like.

First, the SQL:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
CREATE TABLE [dbo].[ETL_Log]
(
  [Log_ID] INT NOT NULL PRIMARY KEY IDENTITY(1,1),
  [StartTime] DATETIME2(7) NOT NULL,
  [PackageName] VARCHAR(255) NOT NULL,
  [LogText] VARCHAR(255) NOT NULL,
  [RowCount] INT NULL,
  [ErrorText] VARCHAR(MAX) NULL,
  [EndTime] DATETIME2(7)
)
 
--Stored procedure for logging the start of a package execution
 
CREATE PROCEDURE [dbo].[s_Log_Start]
  @PackageName VARCHAR(255),
  @LogText VARCHAR(255)
AS
  BEGIN
 
    INSERT ETL_Log (PackageName,StartTime,LogText) 
    VALUES (@PackageName,SYSDATETIME(),@LogText)
 
    RETURN @@IDENTITY
 
  END
 
--Stored procedure for logging the end of a package execution
 
CREATE PROCEDURE [dbo].[s_Log_End]
  @Log_ID INT,
  @LogText VARCHAR(255),
  @RowCount INT = NULL,
  @ErrorSource VARCHAR(MAX) = NULL,
  @ErrorText VARCHAR(MAX) = NULL
AS
  BEGIN
 
  UPDATE ETL_Log 
    SET 
      EndTime = SYSDATETIME(), 
      LogText = @LogText, 
      [RowCount] = @RowCount,
      ErrorText = CASE WHEN ErrorText IS NULL THEN @ErrorSource + ': ' + @ErrorText 
        ELSE ErrorText + CHAR(13) + CHAR(10) + @ErrorSource + ': ' + @ErrorText END 
    WHERE Log_ID = @Log_ID
 
  END
 
--Finally, a view for easy inspection of the log
 
CREATE VIEW [dbo].[v_ETL_Log]
  AS 
    SELECT 
      StartTime,
      EndTime,
      CASE 
        WHEN (SELECT Max(StartTime) FROM ETL_Log)>StartTime 
        THEN DATEDIFF(ss,StartTime,EndTime)
        ELSE DATEDIFF(ss,Starttime,ISNULL(EndTime,SYSDATETIME()))
      END AS DurationSeconds,
      PackageName,
      LogText,
      [RowCount],
      CAST(ISNULL([RowCount],0)*1000.0/
        NULLIF(DATEDIFF(ms,StartTime,EndTime),0) AS INT) AS RowsPerSec,
      ErrorText FROM ETL_Log

Now, with BIML, it is finally easy to share SSIS code snippets on my blog, so here is the BIML part.

First, the variables:

1
2
3
4
<Variables>
  <Variable Name="Log_ID" DataType="Int32">0</Variable>
  <Variable Name="Log_RowCount" DataType="Int32">0</Variable>
</Variables>

For the error handling, I use an OnError Event task on the package level:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<Events>
  <Event Name="EH_OnError" EventType="OnError" ConstraintMode="Linear">
    <Tasks>
      <ExecuteSQL Name="SQL_OnErrorLog" ConnectionName="YOUR_OLEDB_CONNECTION" IsStoredProcedure="true">
      <DirectInput>EXEC s_Log_End ?, 'Package Failed', ?, ?, ?</DirectInput>
        <Parameters>
          <Parameter Name="0" VariableName="User.Log_ID" DataType="Int32" Direction="Input"></Parameter>
          <Parameter Name="1" VariableName="User.Log_RowCount" DataType="Int32" Direction="Input"></Parameter>                
          <Parameter Name="2" VariableName="System.SourceName" DataType="String" Direction="Input"></Parameter>
          <Parameter Name="3" VariableName="System.ErrorDescription" DataType="String" Direction="Input"></Parameter>
        </Parameters>
      </ExecuteSQL>
    </Tasks>
  </Event>
</Events>

At the beginning of the control flow of the package i use an Execute SQL task:

1
2
3
4
5
6
7
<ExecuteSQL Name="SQL_StartLog" ConnectionName="YOUR_OLEDB_CONNECTION" IsStoredProcedure="true">
  <DirectInput>EXEC ? = s_Log_Start ?,'Package Started'</DirectInput>
    <Parameters>
      <Parameter Name="0" VariableName="User.Log_ID" DataType="Int32" Direction="ReturnValue"></Parameter>
      <Parameter Name="1" VariableName="System.PackageName" DataType="String" Direction="Input"></Parameter>
    </Parameters>
</ExecuteSQL>

In the data flow, use this transformation as the last step before your final destination:

1
<RowCount VariableName="User.Log_RowCount" Name="ROWCOUNT_Task" />

And finally, as the last step of the control flow:

1
2
3
4
5
6
7
<ExecuteSQL Name="SQL_EndLog_Success" ConnectionName="YOUR_OLEDB_CONNECTION" IsStoredProcedure="true">
  <DirectInput>EXEC s_Log_End ?,'Package Completed', ?</DirectInput>
    <Parameters>
      <Parameter Name="0" VariableName="User.Log_ID" DataType="Int32" Direction="Input"></Parameter>
      <Parameter Name="1" VariableName="User.Log_RowCount" DataType="Int32" Direction="Input"></Parameter>
    </Parameters>
</ExecuteSQL>

What I like about this setup:

  • It’s super fast to log to
  • It’s super fast to query (If you have a gigantic number of package executions, add an index to StartTime)
  • The table stays small (1 row per package execution)
  • You can see if a package is running, and for how long it has been running (via the view)
  • It logs ALL error messages from the package, and logs them only once. Basically all the “red dots” in the progress window in BIDs
  • The log is easy to query later to see for which packages the row count and/or the performance changes
  • By using BIML, it’s easy to add information to the log later for all packages if requirements change
  • It works for all versions of SSIS and SQL Server without any changes

Finished LogTable