Hand off your change tracking or auditing requirements to SQL Server 2008

If you are writing additional code to satisfy regulatory compliance or auditing requirements, consider new features of SQL Server 2008. Perhaps your custom applications must support compliance with HIPAA, OFCCP, or other regulations. You may be saving additional copies of your data in the database to satisfy those regulations.

Enter SQL Server 2008, assuming it's easy for you to upgrade to that latest release (and it probably is, if you are already on SQL Server 2005). Its new Change Data Capture (CDC) feature copies the data for the tables you specify. While designed to support ETL processes for efficient loading of data warehouses or marts, it can serve well in scenarios where much of the data changes must be captured and stored in separate tables. Here's a data flow diagram in data warehousing scenario:

Also, a Channel 9 webcast goes into technical details about setting up and using CDC, but this is what's in it for you:

• If you use CDC you can forget about writing and maintaining additional code just for compliance purpose.
• You stop worrying whether your additional database updates slow down the performance of core functions, as CDC tracks data asynchronously. (If you have doubts about performance check out this case study about Austrian sports broadcasters.)
• You don't have to prove to anyone that your compliance code does what it's supposed to, as you can point to the well-defined and published features of your database that you simply turn on.
• You can reliably access captured data in a relational format. Already there are tools that help you manage the data from CDC: http://www.codeplex.com/CDCHelper.

On the other hand, you may not need to save detailed history of all updates to your data. Perhaps you only need to know that data in certain columns changed or track the user making the changes. Then consider Change Tracking or Auditing features of SQL Server 2008.

SSIS Package Layout

If you produce SSIS packages for a customer, even if that "customer" is the company you work for, then this tip is for you.

When you create a new SSIS package and start adding items to the design surface you can quickly create a mess if you're not careful.  Even if you are careful it is still quite difficult to precisely place the items so that the connecting lines line up.

For example, I quickly created a package which performs some common tasks:

As you can see, the various items are positioned on the design surface in a haphazard way.  You can spend time dragging the items around until you get a layout that looks better, or you can use the power of the "Layout" toolbar to assist you.

By default, the "Layout" toolbar is not displayed in BIDS.  To display it, right-click on an empty part of the toolbar and click on Layout (or you can click on "Tools | Customize..." and click the check box next to Layout):

  You should see the Layout toolbar displayed:

To fix the layout of my package, I first selected all of the items.  You do this either by clicking on the design surface and dragging the selection box around all of the items or by shift-clicking on each of the items until they are all selected.  Look closely at each of the items--one item will have white "grab handles" and the rest will have black grab handles.  The one with the white handles acts as the anchor for the items in the Layout tool bar that act on multiple items.  For example, if you click on the "Align Lefts" button, the left sides of each item will be set to the left position of the anchored item.

I used a combination of "Align Lefts", "Increase Vertical Spacing", and "Make Vertical Spacing Equal" to align my items in a vertical column:

It took my just a handful of clicks and about 20 seconds to perfectly align the items for a professional look.

If you have auto-sized any of your shapes, you are still in luck.  Instead of "Align Lefts" you can choose "Align Centers" to keep the arrows straight.

I'm sure that you have your favorite way to lay out your items in your packages, such as a cascading look where the arrows go down, over, and then down again.  While that isn't directly supported by the layout toolbar you can still make use of the toolbar to "Make Vertical Space Equal" after moving your items where you want them.

Leave a comment if you have additional tips for using the Layout toolbar with SSIS packages.

Farewell.

Capturing LINQ Output

If you use LINQ to SQL in your projects, you may find yourself wondering just what actual SQL gets generated and executed against your database.  If you're a DBA, you'd probably break out the SQL Profiler.  But if you come from a development background, or are just not adept at performing SQL traces, then there is a code-only solution for you.

It turns out that the DataContext class exposes a property called Log, which allows you to specify a System.IO.TextWriter which will receive the SQL commands which are generated.

In a previous post I showed how to call a stored procedure from LINQ.  In this post I'll show the SQL calls produced.

The first call in that project was to load all the managers in the database.  I defined a manager as a person who had 1 or more "Employees" associated with him or her.  As a reminder, the LINQ query looked like this:

   1: var managers = (from m in dc.Employees

   2:                 where m.Employees.Count() > 0

   3:                 select new EmployeeData

   4:                 {

   5:                     Name = string.Format("{0}, {1}",

   6:                         m.Contact.LastName,

   7:                         m.Contact.FirstName),

   8:                     ID = m.EmployeeID

   9:                 }).Distinct();

I changed the code to write the output to a temporary file by adding one line of code, like so (make sure you have the AutoFlush property set to true, or flush the Log property manually, otherwise you may not see any messages in the file):

   1: dc.Log = new System.IO.StreamWriter(@"C:\Temp\LinqOutput.txt", true, UTF8Encoding.UTF8) { AutoFlush = true };

Now when the code is run the log file in my C:\Temp folder has the following in it:

   1: SELECT DISTINCT [t2].[EmployeeID] AS [ID], [t2].[value], [t2].[LastName] AS [arg0], [t2].[FirstName] AS [arg1]

   2: FROM (

   3:     SELECT [t0].[EmployeeID], @p0 AS [value], [t1].[LastName], [t1].[FirstName]

   4:     FROM [HumanResources].[Employee] AS [t0]

   5:     INNER JOIN [Person].[Contact] AS [t1] ON [t1].[ContactID] = [t0].[ContactID]

   6:     ) AS [t2]

   7: WHERE ((

   8:     SELECT COUNT(*)

   9:     FROM [HumanResources].[Employee] AS [t3]

  10:     WHERE [t3].[ManagerID] = [t2].[EmployeeID]

  11:     )) > @p1

  12: -- @p0: Input NVarChar (Size = 8; Prec = 0; Scale = 0) [{0}, {1}]

  13: -- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [0]

  14: -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1

Here you can see exactly what gets sent to SQL Server.  Let's take a look at how a stored procedure is called (which was the point of my last blog post).  After modifying the code to set the Log property, here are the results:

   1: EXEC @RETURN_VALUE = [dbo].[uspGetManagerEmployees] @ManagerID = @p0

   2: -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [3]

   3: -- @RETURN_VALUE: Output Int (Size = 0; Prec = 0; Scale = 0) [Null]

   4: -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1

As you can see, LINQ "knows" how to call stored procedures with parameters and can return a return value as well.

If you don't want to write to a file, then anything that derives from a System.IO.TextWriter will do, including your own classes.  One class you might find value in implementing is one that overrides the Write method and in turn calls System.Diagnostics.Debug.Write.  If an instance of this class is assigned to the Log property of your DataContext class then you can see the SQL directly in the output window of Visual Studio (or other debug viewer programs).

Leave a comment if you have used this functionality in other creative ways!