Well it's 2008 so I better post another Blog entry. Just a quick note though as after tearing my hair out for best part of a day, I have found an interesting 'feature' with BIT Fields in SQL Server 2005 and a SSIS Data Flow OLE-DB Source (though I suspect it occurs elsewhere within SSIS and maybe SSMS, and the behaviour did not occur when I ran something interactively, it only occurred when the SSIS Package was executed as Job i.e a real **&^&%$ to find
Basically, OLE_DB Source query was doing the following in a derived table against a SQL Server 2005 database
SELECT Column FROM dbo.Table WHERE ColumnFlag = 1;
The ColumnFlag was defined as a BIT field on a Table. However, when I ran the code via a Job I was finding it effectively ignored the WHERE ColumnFlag = 1 filter. I wondered if this was because in SQL Server 2005 Microsoft have changed the behaviour of BIT fields and introduced the new keywords 'TRUE' and 'FALSE' - Sure enough, as soon as I changed the code to be:
SELECT Column FROM dbo.Table WHERE ColumnFlag = 'TRUE';
It worked as I wanted.
So I have decided that whenever I use BIT Fields in SQL Server 2005 I will now use 'TRUE' and 'FALSE' - Not 1 and 0, another convention change I will just have to get use to until it becomes second nature Like ANSI 92 and Statement Termination;
Note: 'TRUE' and 'FALSE' will n ot work against a SQL Server 2000 database, you will need to use the old convention