January 2006 - Posts

Professional SQL Server 2005 Integration Services Finally Ships!

Whew, what a long road but our book is finally being shipped to bookstores and out from Amazon this week. The book is a tutorial fashion book for SSIS and you can download one of the chapters in PDF format below. 

* - Buy the book on Amazon.
* -
Download Chapter 5 - Creating an End-to-End Package

-- Brian Knight

South Florida CodeCamp

Bayer White and I will be doing 5 sessions at the South Florida Code Camp. If you're in the area, please come see us for a free full day of geekdom! South Florida .NET CodeCamp - www.fladotnet.com/codecamp - some speaker slots open (please review available tracks before submitting) - Space is limited, register now! All registration and speaker info available at the website.


A Developer's Introduction to Windows Vista by Brandon McMillon of Microsoft
Accessible Web Applications (Section 508) by Wally McClure of Scalable Development, Inc
Advanced Generics by Vinay Ahuja of Idea Integration
ASP.NET using Strongly Typed Datasets by Edward Gnatiuk of Independent
Atlas Introduction by Jim Zimmerman of JimZimmerman.com
Beginners SQL 2005 Mobile Walkthrough by Nikita Polyakov of Microsoft Student Ambassador
Building a Software Factory Assembly Line by Tom Fuller of Publix Super Markets, Inc
Building a Windows Mobile Solution using Visual Studio 2005 by David McNamee of Microsoft
A Primer for Secure Coding (Part I & II) by Brandon McMillon of Microsoft
Building an Office Solution using Visual Studio Tools for Office 2005 by David McNamee of Microsoft
Building new apps with ATLAS by Wally McClure of Scalable Development, Inc
CLR Objects in Sql Server 2005 by Wally McClure of Scalable Development, Inc
Conversion to ASP.NET 2.0 Birds of a Feather by Dave Noderer of Computer Ways, Inc.
Customizing the Windows Forms DataGridView by Ken Tucker of VB-TIPS.COM
Disconnected Data Strategies by Steve Joubert of ASPSOFT Inc.
DotNetNuke - Birds of a Feather by Dave Noderer of Computer Ways, Inc. and Tracy Wittenkeller of T-WORX, Inc.
Ensure A Seamless Upgrade Path to WCF with ASMX 2 and WSE 3 by Tom Fuller of Publix Super Markets, Inc
Fishin' spots with SQL2005 and Virtual Earth by Joe Healy of Microsoft
For Love or Money : Your IT Career by Alex Funkhouser of Computer Careers Staffing, Inc
Game Development in .NET by Jose Fuentes of Navin Technologies
GeekGurl’s Goodies: Creating a Consistent Layout using Master Pages in ASP.NET 2.0 by Raenell Garner of Perot Systems Corporation
GeekGurl’s Goodies: Personalization using Web Parts in ASP.NET 2.0 by Raenell Garner of Perot Systems Corporation
GeekGurl’s Goodies: Using Data Source Controls in ASP.NET 2.0 by Raenell Garner of Perot Systems Corporation
Getting Started with Real World SQL Server Integration Services by Brian Knight of Idea Integration \ SQLServerCentral.com
Getting Started with Visual Studio.NET Express by Brandon McMillon of Microsoft
Getting to Know the .Net Framework Class Library by Pierre Donyegro of Southern Wine and Spirits
Getting to Know Windows Workflow Foundation by Bayer White of Idea Integraton
Hacking SQL Server by Brian Knight of Idea Integration \ SQLServerCentral.com Bayer White of Idea Integraton
HTML-Based DotNetNuke Skinning by Tracy Wittenkeller of T-WORX, Inc.
Image Capture with Windows Image Acquisition Library by Ed Hill of DeVry University
Information Bridge Framework by David McNamee of Microsoft
Integrating Data Mining into your Application by Brian Knight of Idea Integration \ SQLServerCentral.com and Bayer White of Idea Integraton
Integrating the Dark Art of DHTML with ASP.NET 2.0 by Steve Joubert of ASPSOFT Inc.
It’s all about the Data: Building a Datawarehouse using SQL Server 2005 by Wes Dumey of Health Intelligence Systems
New Controls in ASP.NET 2.0 by Steve Joubert of ASPSOFT Inc.
Programming the SQL Common Language Runtime by Joe Homnick of Homnick System, Inc.
Real World AJAX and other client scripting hacks by Jim Zimmerman of JimZimmerman.com
Reporting Services for Developers by Bayer White of Idea Integraton and Brian Knight of Idea Integration \ SQLServerCentral.com
Service Orientation – What it REALLY Means! by David McNamee of Microsoft
Smart Client Application Development & Deployment by Shawn Weisfeld of Orlando .NET User Group
SOA Design Strategies: Adhering to the 4 Tenets by Tom Fuller of Publix Super Markets, Inc and David McNamee of Microsoft
The Psychology of Working as a Consultant or Permanent Employee by JB Hampton of Millenium Consulting
Three “Views” in ASP.NET 2005 by Richard Li of The Berkley Group
Understanding Master Pages and Themes. (What’s the Difference? How Can They Help You?) by Steve Joubert of ASPSOFT Inc.
Using .NET Generics by Vinay Ahuja of Idea Integration
Visual Web Developer Express Walkthrough by Nikita Polyakov of Microsoft Student Ambassador
XML Rocks in SQL Server 2005!!!! by David Silverlight of XMLPitstop.com

Proper Case for Data in a SSIS Script Component Transform

Well I feel like a college professor that shows the student a five page math problem and then follows up with a single button you could hit in your calculator to do the same thing. I used to hate that and now I'm that guy :). Anyways, in yesterday's post I showed how to do string conversions with the proper case (first letter in every word capitalized) by using regular expressions. While that was a pretty elogate way to do it, you can perform the same function with a simple built-in function called VbStrConv.ProperCase in the SSIS data flow. This function lets you take the input of a string and converts the string to the proper case. In the following code, you can see I have the input column called InputName and then I massage the data and output the value of OutputName.

The code then shrinks down to a single important line shown below in the commented line:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent

   Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
      'This is the line that performs the magic to Proper Case.
      Row.OutputName = StrConv(Row.InputName, VbStrConv.ProperCase)
   End Sub

End Class

PS: Special thanks to Toby Rogers for catching how easy this would be to use VbStrConv instead of Regular Expressions!

-- Brian Knight

Script Transform Component to Cleanse Data

In yesterday's post, I showed you how to cleanse data using the Script Component acting as a transform. Let's take it a lot farther by doing some more advanced expressions. In this very typical scenario, you receive data that's all upper case from a mainframe. You want to cleanse the data to where the first letter of every word is upper case and everything else is lower case. To do this, drag over the Script Component and select Transform. Check the input column that you wish to send into the script. For my example, let's use the input name of InputName. I'm going to send into the pipeline a column called OutputValue. I add this column in the Inputs and Outputs page.

Once this is selected, you can select Design Script on the Script page. The script will look like something like the below script. In order to make things a bit simplier,  you'll want to import the System.Text.RegularExpressions namespace. As you can see, I'm using RegularExpressions to parse through the string and find when one word ends and another begins. You'll want to make sure you cleanse the input for Nulls before that chunk of the code executes. The MatchHandler function takes the two groups of strings and upper cases one and lower cases another.

All you would have to essentially do to use this code is change the InputValue and OutputValue columns in the code and you're ready to go.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Text.RegularExpressions

Public Class ScriptMain
Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

   Dim myDelegate As New MatchEvaluator(AddressOf MatchHandler)
   Dim strpattern As String = "\b(\w)(\w+)?\b"

   Dim re As New Text.RegularExpressions.Regex( _
   strpattern, RegexOptions.Multiline
Or _
   RegexOptions.IgnoreCase _

'Will check to see if there is a NULL value before trying the function.

If Row.InputName_IsNull = False Then
 Row.OutputName= re.Replace(Row.InputName, myDelegate)
End If

End Sub

Private Function MatchHandler(ByVal m As Match) As String
   Return m.Groups(1).Value.ToUpper() & m.Groups(2).Value.ToLower()
End Function


It's important to note that I'm not a coder so I'm sure my code may not be the most prestine. :)

For more on how to use the Script Component as a transform, see yesterday's post.

Update: A much cleaner approach to doing proper case was sent to me by Toby Rogers and can be seen in this post.

-- Brian Knight

How to use a Script Transformation

Using a Script transformation is a handy way to extend what transforms are available to you. For example, picture social security data that goes into your Script transformation unencrypted and then out encrypted. Typically, you can also use this type of custom script to extend what a Derived Column task can do. If you have the choice between the two though, always choose a Derived Column.

A good extension of a Derived Column transformation into a Script transformation would be where you need to replace certain text with other text. If you have only two options, you could use the REPLACE expression inside a Derived Column transform. When you go above two, the Replace expression becomes less elegant. So, in comes the Script transform.

After connecting the Script transform to the data flow, go to the Input Column task for the transform and check the columns that will be sent into the script as an input. Next, go to the Inputs and Outputs tab and click Add Output. Name the columns that you wish to output. I’m going to call this one OutputValue for this example.  Next, click Design Script.

In this script you can see that first we localize the variable. Because I can’t predict whether the user inputted mixed, lower or upper case, I use the UCASE function to upper case the input value for comparison purposes. The word InputValue in Row.InputValue would be replaced with whatever you checked in the input screen. Then, the main thing to remember is Row.OutputValue will set the OutputValue output we set earlier will be set to the new value. So this code will take the input of whatever the color is and translate it to the hex value. Then, the OutputValue output will be presented to the pipeline and can be consumed by the destination or the next transform in the pipeline.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim HexValue As String

Select Case UCase(Row.InputValue)
Case "RED"
      HexValue = "#FF0000"
   Case "YELLOW"
      HexValue = "#FFFF00"
   Case "GREEN"
HexValue = "#008000"
End Select

Row.OutputValue = HexValue
End Sub

-- Brian Knight 

Renumber PackageIDs

I'm now a huge advocate of using package templates. For more info on how to use them see this post. Template provide a handy way to determine the best practices for your company and reuse those over and over again. Well there's one slight problem with using them. The package that uses the template inherits the PackageID of the template. Why is this a problem? Well, if you're using the System::PackageID variable for logging, all of your packages now will report that they are the template package.

So, you have two options. Ultimately, you must renumber each package to a unique value. You can do this manually in the package Properties pane. You can also do this in an automated method by using the dtutil.exe application and the /I switch as shown below:

dtutil.exe /I /FILE "PackageName.dtsx"

The best way I feel is to create a batch file with the following code that will loop through every package in a directory and renumber it:

for %%f in (*.dtsx) do dtutil.exe /I /FILE "%%f"

Please note though that once you use DTSutil.exe to do this, it will re-arrange your package. Don't worry, your package will still work but the appearance may be a bit rearranged.

Update 1/7/06: If you think this behavior should be fixed, please vote for this to be added into a service pack here: http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=c1394ab2-7e90-47de-a057-d2d183da74a2

-- Brian Knight

Pubs and Northwind databases not installed in SQL Server 2005

I love the new Microsoft SQL Server 2005 sample databases (AdventureWorks and AdventureWorksDW). Compared with the Northwind and even worse the Pubs databases, they're amazingly more real-world. They show best practices and demostrate a good portion of the 2005 features. The problem lies in a lot of the SQL Server 2000 examples on the Internet still work in SQL Server 2005 and content that's specific to 2005 is still being produced. So, what to do while you wait for the examples to be updated? Why not live like it's 2002 and install the SQL Server 2000 sample databases? That way the sample RDL files, T-SQL scripts and most other examples on the net will work.

To do this, you'll need to download them at: http://www.microsoft.com/downloads/details.aspx?familyid=06616212-0356-46a0-8da2-eebc53a68034&displaylang=en

Run the SQL2000SampleDb.msi file to extract the files to the strangely placed directory C:\SQL Server 2000 Sample Databases (no you can't change this I'm afraid). Then you can run the scripts in the directory to install the databases or attach the data files. Instpubs.sql creates the pubs sample database and Instnwnd.sql creates the Northwind sample database. If you don't have the SQL tools installed, you can use SQLCMD from a DOS prompt to install them as shown below:

c:\sqlcmd -S .\InstanceName -i instpubs.sql
c:\sqlcmd -S .\InstanceName -i instnwnd.sql

If you do have the tools, just run the scripts in the Query Window of SQL Server Management Studio. The MDF and LDF files are there for attachment. Another method is to right-click on the database tree and click Attach, pointing to the MDF and LDF files. Before doing this though copy the files to the instance's Data directory. After the attachment or script is run, you can remove the directory and files through Add\Remove Programs.

PS: Don't get in the habit of creating new scripts against these databases, as they no longer represent the current technology strategies you would employ.

-- Brian Knight

Great SSIS Best Practice Post

Jamie Thomson has posted a great blog post about SSIS development best practices. I love his naming standard idea:


Jamie recommends that you prefix each task or transform with a prefix that indicates what type of task or transform it is. For example, if you have a task that loads an inventory table, you would call the Data Flow task "DFT Load Inventory". This obviously would not help you at design time as you can see by the icon what type of task it is. It would however help you and the operations folks at your company would be able to track down what type of task failed easily. For example, if you use a log provider of some sort to output all the events, the Source Name will show the task type versus just Load Inventory (and the ops guy wouldn't know what this task is).

-- Brian Knight