SQL Server

Query to fetch table sizes and row counts quickly

Here’s a query that comes in handy when checking the size of your tables within a SQL Server Database:

SELECT st.name AS TableName,
	(SELECT SUM(max_length) FROM sys.columns WHERE object_id=st.object_id) AS RowLength,
	si.rowcnt AS [RowCount],
	CAST(si.rowcnt*(SELECT SUM(max_length) FROM sys.columns WHERE object_id=st.object_id)/1024.0/1024.0 AS INT) AS MaxTableSizeMB
FROM sys.tables as st
LEFT JOIN sysindexes si on st.object_id=si.id and si.indid < 2
ORDER BY st.name

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

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