Alberto Ferrari : SQL, SQL Server

SSIS: living in a parallel world? Not yet...

AlbertoFerrari — Sun, 14 Sep 2008 19:57:00 GMT

Today I come into a nice bug (misfeature?) of SSIS 2008 and I'd like to share it with you. Let's start from the beginning:

One of the keys to make something speedy is to parallelize it. In TableDifference I read from two inputs, compare them and produce several outputs for new, deleted, updated rows and so on. It is the classical "produce/consumer" paradigm that works fine in parallel, creating a consumer thread that compares rows and different threads to produce them.

During the porting of the component in SQL 2008 I discovered that my code does not work as expected. It reads all the lines, then terminates abnormally without producing a single row of output. It took me a couple of hours and several coffes to discover the change in the way SSIS handles asyncronous components causing my bug.

In order to make the problem easier to understand (for microsoft connect too), I produced the same behaviour with a much simpler script component. Imagine to have a script transformation that needs to make some very complex work on each row. You will end up with something like this:

    public override void Input0_ProcessInputRow(Input0Buffer Row) {
        Output0Buffer.AddRow();
        Output0Buffer.LineID = VeryComplexFunction(Row);
        if (Row.EndOfRowset ()) {
           OutputBuffer.SetEndOfRowset ();
        }
    }

But, if you have a 64 CPU computer. You can imagine to detach many threads, each one handling just one very complex function, in this way you will produce a massive parallel computation. Wow! The solution to get maximum speed would be this:

    public override void Input0_ProcessInputRow(Input0Buffer Row) {
        Thread T = new Thread(SendAsyncRow);
        T.Start(Row.EndOfRowset());
    }

    public void SendAsyncRow(Object O) {
        Output0Buffer.AddRow();
        Output0Buffer.LineID = VeryComplexFunction();
        if ((bool)O == true) {
            Output0Buffer.SetEndOfRowset();
        }
    }

You detach a separate thread for each row, the thread will compute the very complex function and send it to the output buffer as soon as it is ready. In order to make SSIS understand when everything is finished, you signal SetEndOfRowset in the last thread.

Don't start crying, this code will never work, the SetEndOfRowset is called on the last read line and not on the last produced one, but this is just pseudocode to understand the topic, don't blame me for inconsistencies. :) Let's go on with the main topic.

Under SSIS 2005 this code (in VB, of course) would work fine as the engine detects the end of processing when it receives the EndOfRowset on the outputs. In SSIS 2008 the engine detects the end of the processing as soon as the last call to ProcessInputRow has finished. So, in our case, after the last thread has been detached SSIS believes that the processing is finished and calls the cleaning up of the component, completely ignoring both the presence of separate threads running and our williness to not terminate the component because we know that we need some more time.

The result? As with any other parallel bug you will sometime get a crash, on the same inputs sometime finish corectly, sometime get an error... the nightmare of any programmer.

If you want to try it by yourself, cut and paste this code to a transformation script in a data flow that process some lines ( rows 10/20 lines are enough):

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

using System.Threading;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent {

    bool useThreads = false;

    /// <summary>
    /// 
    /// Processes input rows
    /// 
    /// </summary>
    /// <param name="Row"></param>
    public override void Input0_ProcessInputRow(Input0Buffer Row) {
        Thread T = new Thread(SendAsyncRow);

        if (useThreads) {
            T.Start(Row.EndOfRowset());
        } else {
            SendRow();
        }
    }

    /// <summary>
    /// 
    /// Sends a row in sync mode
    /// 
    /// </summary>
    private void SendRow() {
        // Thread.Sleep(100);
        Output0Buffer.AddRow();
        Output0Buffer.LineID = VeryComplexFunction();
    }

    /// <summary>
    /// 
    /// Sends a row in async mode
    /// 
    /// </summary>
    /// <param name="O"></param>
    public void SendAsyncRow(Object O) {
        Thread.Sleep(100);
        Output0Buffer.AddRow();
        Output0Buffer.LineID = VeryComplexFunction();
        if ((bool)O == true) {
            Output0Buffer.SetEndOfRowset();
        }
    }

    /// <summary>
    /// 
    /// Not very complex... but it's a demo!
    /// 
    /// </summary>
    /// <returns></returns>
    private int VeryComplexFunction() {
        return 10;
    }
}

using Serial mode (useThreads se to false) everything works fine. Using parallel mode (useThreads set to true) SSIS will crash and produce no output at all. The bug in TableDifference was very similar to that, I needed to handle the wait for termination of the consumer process during the ProcessInput function.

It is not the first time I discover inconsistencies in the way SSIS handles parallelism, in order to try to make it easier to write code (using 2008 mode there is no need to call SetEndOfRowset and your program will run fine if you forget it) the architects of SSIS introduce a behavior that will make better and correct code crash. If you believe this is a problem, vote here, anyway... be aware of it, it mighe be useful in the future. :)

Visio and SQL Extended Properties: a tool to marry them

AlbertoFerrari — Wed, 07 Nov 2007 10:00:32 GMT

Visio database models have column descriptions, SQL Server has extended properties where to store them but, even if they share the same producer, it seems that Visio is unable to store column descriptions in extended properties so that other tools can gather the same information easily and produce a decent documentation of a database.

After some browsing on the web, I was unable to find a tool that let Visio save the table and column descriptions in SQL Server, so I decided it was time to code it by myself.

First you need to generate the DDL script of the database having DDL Script commens ON for all the columns (which can be configured in the driver options) as in the following picture:

In the DDL file, VISIO adds header of comments to each table like this:

/* Create new table "Sales.Dim_Customers".                                         */
/* "Sales.Dim_Customers" : Contains all the customer with various attributes used  */
/* to analyze them. Does not contain (bla bla bla)                                 */
/* "ID_Customer" : Primary key of the table                                        */
/* "CustomerCode" : Customer code as seen in the OLTP database                     */
/* "ID_Geography" : Key in Dim_Geography. It is not shown to the end user          */
/* "MaritalStatus" : Description of the marital status (Single / Married)          */
/* "Gender" : Description of the gender (Male, Female)                             */
... ... ...
create table "Sales.Dim_Customers" ( 
"ID_Customer" int identity not null,
"CustomerCode" nvarchar(15) not null,
"ID_Geography" int not null,
"MaritalStatus" nvarchar(20) not null,
"Gender" nvarchar(20) not null)

I have written a simple parser (GenerateExtendedAttrib) that looks for comments in this form in a DDL file (starting with "Create new table", then checks for column names and so on) and will finally generate the sp_addextendedproperty calls to define all the column and table descriptions.

The code fragment above generates this:

EXEC sys.sp_addextendedproperty 
    @name=N'MS_Description', 
    @value=N'Contains all the customer with various attributes used to analyze...' , 
    @level0type=N'SCHEMA',
    @level0name=N'Sales', 
    @level1type=N'TABLE',
    @level1name=N'Dim_Customers';
EXEC sys.sp_addextendedproperty 
    @name=N'MS_Description', 
    @value=N'Primary key of the table' , 
    @level0type=N'SCHEMA',
    @level0name=N'Sales', 
    @level1type=N'TABLE',
    @level1name=N'Dim_Customers', 
    @level2type=N'COLUMN',
    @level2name=N'ID_Customer';

You can call the utility with "/Append" in order to have the property definitions appended to the original file (which, in turn, can be run to have the database created) or you can call it without parameters, in this case the code is written to the console and you can redirect it wherever you want.

The tool can be downloaded at www.sqlbi.eu and is provided with sources (it is indeed a very simple yet effective gadget) so you can adapt it to whatever your needs are.

Have fun and, if you make any change to the code that might be useful, send me a note so I can update the public release.

SQL: Merge two "history tracking" tables

AlbertoFerrari — Mon, 15 Oct 2007 18:36:00 GMT

If you have two columns in a table that "remember" their old values in two different historical tables, as it is the case in AdventureWorks with EmployeePayHistory and EmployeeDepartmentHistory, you may need (or just wonder how) to merge these two tables into only one historical table that will keep track of both columns.

This has been my "problem of the day" and, after some thoughts, I came up with a pattern of solution that I'd like to share with you both to see if you have a better solution and to have the code at hand when I'll need later. :)

Before using the real table, I made up a simple test case with a Product table with only two fields a couple of variation tables (VariationA and VariationB):

--
--  Sample table Products, only two columns A and B. The current value of the row is (D, 4)
--
CREATE TABLE Products (
    A CHAR (1),
    B INT
)
INSERT INTO products (A, B) VALUES ('D', 4)

--
--  Variations for column A
--
--      ------------------
--      Year    Old Value
--      ------------------
--      2001        A
--      2002        B
--      2003        C
--      2005        D
--
CREATE TABLE VariationA (   
    Position INT,
    OldValue VARCHAR (5),
    ChangeDate DATETIME )

INSERT VariationA (Position, OldValue, ChangeDate) VALUES (1, 'A', '01/01/2001')
INSERT VariationA (Position, OldValue, ChangeDate) VALUES (2, 'B', '01/01/2002')
INSERT VariationA (Position, OldValue, ChangeDate) VALUES (3, 'C', '01/01/2003')
INSERT VariationA (Position, OldValue, ChangeDate) VALUES (4, 'D', '01/01/2005')

--
--  Variations for column B
--
--      ------------------
--      Year    Old Value
--      ------------------
--      2000        1
--      2003        2
--      2004        3
--
CREATE TABLE VariationB (   
    Position INT,
    OldValue INT,
    ChangeDate DATETIME )

INSERT VariationB (Position, OldValue, ChangeDate) VALUES (1, 1, '01/01/2000')
INSERT VariationB (Position, OldValue, ChangeDate) VALUES (2, 2, '01/01/2003')
INSERT VariationB (Position, OldValue, ChangeDate) VALUES (3, 3, '01/01/2004')

The query that mixes all these variations into a single table is pretty easy but it took me some time to discover. My solution is to detect what was the value of "B" when a variation in "A" happened and vice versa. As the values stored are "old" values, we know that the value of A at a certain date is the value stored in the first variation for A AFTER that date. The only special case is the current time: if no variation record is found then we know that the value to use is the current value of the record in the product table. The same is obviously true for B.

I ended up with this pattern query:

--
--  AllEvents contains all the events from both VariationA and VariationB,
--  and will detect, for each variation that happened, what was
--  the value of the other column at that time, building in this way the
--  merged variation list.
--  The final SELECT will return the sorted and DISTINCTed result. 
--  Then final COALESCE is needed because if a value is NULL it 
--  means that it should contain the "current" value of the column
--
WITH AllEvents AS (
    SELECT 
        OldValueOfA  = Events.OldValueOfA,    -- Old value of A
        OldValueOfB  = Events.OldValueOfB,    -- Old value of B
        ChangeDate   = Events.ChangeDate      -- Date of change
    FROM 
            (SELECT 
                OldValueOfA = OldValue, 
                OldValueOfB = (SELECT TOP 1 OldValue 
                                 FROM VariationB V
                                WHERE V.ChangeDate >= VariationA.ChangeDate
                                ORDER BY ChangeDate), 
                ChangeDate 
            FROM VariationA 
        UNION ALL 
            SELECT 
                OldValueOfA = (SELECT TOP 1 OldValue 
                                 FROM VariationA V
                                WHERE V.ChangeDate >= VariationB.ChangeDate
                                ORDER BY ChangeDate), 
                OldValueOfB = OldValue, 
                ChangeDate 
            FROM VariationB
        ) Events
    )
SELECT DISTINCT
    ChangeDate   = ChangeDate,
    OldValueOfA  = COALESCE (OldValueOfA, (SELECT A FROM Products)),
    OldValueOfB  = COALESCE (OldValueOfB, (SELECT B FROM Products))
FROM
    AllEvents
ORDER By ChangeDate

Clearly, i could have written some VB code in SSIS to solve the same problem. Nevertheless, having a pattern query at hand is useful because the pattern can be easily adapted to any real world situation with only SQL Management Studio at hand. The following code is the implementation of the pattern for AdventureWorks to merge Department and Payment history for Employees:

WITH AllEvents AS (
    SELECT 
        EmployeeID      = Events.EmployeeID,
        OldValueOfRate  = Events.OldValueOfRate,  
        OldValueOfDep   = Events.OldValueOfDep,   
        ChangeDate      = Events.ChangeDate     
    FROM 
            (SELECT 
                EmployeeID      = EmployeeID,
                OldValueOfRate  = Rate, 
                OldValueOfDep   = (SELECT TOP 1 DepartmentID
                                     FROM HumanResources.EmployeeDepartmentHistory V
                                    WHERE V.StartDate >= PayHistory.RateChangeDate
                                      AND V.EmployeeID = PayHistory.EmployeeID
                                    ORDER BY V.StartDate), 
                ChangeDate      = RateChangeDate 
            FROM HumanResources.EmployeePayHistory PayHistory
        UNION ALL 
            SELECT 
                EmployeeID      = EmployeeID,
                OldValueOfRate  = (SELECT TOP 1 Rate
                                     FROM HumanResources.EmployeePayHistory V
                                    WHERE V.RateChangeDate >= DepHistory.StartDate
                                      AND V.EmployeeID = DepHistory.EmployeeID
                                    ORDER BY V.RateChangeDate), 
                OldValueOfDep   = DepHistory.DepartmentID, 
                ChangeDate      = StartDate
            FROM HumanResources.EmployeeDepartmentHistory DepHistory
        ) Events
    )
SELECT DISTINCT
    EmployeeID     = EmployeeID,
    ChangeDate     = ChangeDate,
    OldValueOfDep  = COALESCE (
                         OldValueOfDep,
                         (SELECT DepartmentID 
                           FROM HumanResources.EmployeeDepartmentHistory DepHistory
                          WHERE EndDate IS NULL 
                            AND DepHistory.EmployeeID = AllEvents.EmployeeID)),
    OldValueOfRate = COALESCE (
                         OldValueOfRate,
                         (SELECT TOP 1 Rate
                           FROM HumanResources.EmployeePayHistory PayHistory
                          ORDER BY RateChangeDate))
                    
FROM
    AllEvents
ORDER By EmployeeID, ChangeDate

The code can be easily checked filtering for a single EmployeeID (4 is a good candidate for this check). Please note that - in this case - the "current value" is kept in the history table and so the final COALESCE is a bit more intricated but still pretty easy both to write and to understand.

If you have a better pattern for this kind of situation or any comments on it... I'll be glad to read your comments.