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

Map of Runkeeper Tracks

After using different tracking apps for my runs and bike rides the last months, I wanted to see how it looked like when combined in the same map. After playing around with the TileMill application from mapbox.org I managed to make this neat little map (Pink=Biking, Yellow=Run/Hike):

I have merged the GPX-files from Runkeeper manually for now, but I will try to automate the process a bit more , and maybe store the results in my “Personal DWH”.

Visual Studio (BIDS 2008) crashes when opening Data Flow task

I’ve had this problem several times when working with SSIS-projects developed on another computer. What has worked for me, and just now for a colleague of mine, is to change the DPI in windows to 96 DPI to 120 DPI. Both cases have been with BIDS 2008 and SQL Server 2008 R2, but I wouldn’t be surprised if the bug was present in 2005 as well.

Silly, silly bug

Named sets missing in Dashboard Designer (Performancepoint/Sharepoint 2010)

I had this annoying problem that my named sets in my SSAS cube suddenly disappeared from view in the dashboard designer. The error messages were very vague. When browsing the cube in Excel, everything appeared as intended.

Eventually it turned out that the attribute that the named set was using was set to AttributeHierarchyVisible=False. Changing this back to True solved the problem. I find it very strange that the Dashboard designer hides sets that use hidden attributes.