Todd McDermid's Blog

Data Explorer Rocks - Let's Get an Enterprise Story

2013-04-12T04:00:00.000-07:00

First - it's been a while since I've posted. I have quite a few articles in the pipe, but they're "heavier", so take longer to flesh out and publish. I thought I'd start the ball rolling by commenting on the newly released Data Explorer Preview for Excel. If you are an "information worker" that is copying and pasting data into Excel, then cleaning it up, download and install this now. It definitely has the potential to save you tons of time redoing that work, or remembering what you did, or where you got the data later. There are plenty of resources to help you get started with Data Explorer, with walkthroughs and samples - Bingle to your heart's content. I'll wait.
The Commentary
As most of you know, I'm a DW and SSIS guy, so I'm interested in "enterprise" tools for ETL - things that can be managed, scheduled, scaled, and monitored. So why am I commenting on Data Explorer? First, it is very cool. But my real intention is to question and to elicit some reaction as to the future of Data Explorer. Data Explorer is currently a "self-service" BI tool just like PowerPivot was, but PowerPivot has matured somewhat since v1. PowerPivot workbooks can be transferred (by two methods) from the wild west of self-service to the Victorian rigour of IT governance.
The first method is pretty slick, transparent to business users, and pretty effective. IT simply has to set up their SharePoint farm appropriately with PowerPivot for SharePoint. Business users then simply take advantage of SharePoint's document repositories to collaborate and share. They'll do this of their own accord, because it's much more convenient than email attachments, and because you've berated them often enough about clogging your Exchange server with dozens of copies of a very large workbook distributed to a large number of recipients. What happens in the background is that SharePoint strips out the PowerPivot model from the Excel workbook and loads it into the PowerPivot for Excel shared service. In addition to the sharing benefits (not needing Excel or PowerPivot addin installed), IT immediately gets visibility and manageability of the underlying SSAS tabular model.
The second method is a manual, deliberate process. An IT resource can open the workbook in Visual Studio, extract the PowerPivot tabular model (just like happens in SharePoint), work with it (to extend and/or validate it), then deploy it to an IT-managed SSAS server.
Data Explorer Growth Options
What's the story for Data Explorer? Right now, there doesn't appear to be one, but I'm sure hoping (and asking) Microsoft to think about one. The benefits are quite similar to those achieved by the PowerPivot example. If we can "grow up" the Data Explorer transforms, IT can validate them, stabilize them, and manage data lineage concerns.
Similarly to PowerPivot, I can see a couple options here. First, they could simply create a "Data Explorer for SharePoint" complement to the "Data Explorer for Excel" shared service. When you uploaded an Excel workbook that uses Data Explorer, SharePoint would extract the DE part, store it in some kind of new DE repository/server, and data would be funneled through that server when the workbook was refreshed as an Excel Service. Monitoring and connection management are natural SharePoint extensions. Being able to view and/or edit the transform in a "management portal"... less likely - just open the workbook. Share a DE implementation between workbooks, or surface the DE result as a business data connection? I can see the appeal.
But that first option is likely to be unappealing to a good portion of IT shops, for much the same reason that the first PowerPivot option is. It's not using tooling they're already familiar with and skilled up for. It's not "enterprise-grade". And it's baked into SharePoint, which has a particularly bad stink to SQL Server professionals.
What I'd like to see is the ability to extract the Data Explorer bits from a workbook into SSIS. What would this look like? I can see two options - both of which would have to be a ways off in development, simply because Data Explorer isn't done yet.
First, I could see a "translation" layer, where the DE "steps" are converted into native SSIS data flow components. Some of these components may not exist now (depending on what DE steps ultimately get shipped). There would be some development required on the SSIS side to address that.
Second, I could see new DE components created for SSIS - possibly replacing or duplicating current components. That's a bigger job (I think), but may give Microsoft the opportunity to rationalize both tools at the same time. I don't know if they're interested in doing that.
Final Thoughts
One thing that all of the above still doesn't solve - and this is a little off-topic - is custom transforms. I don't currently see a method in DE to create your own transform. That's probably a little beyond your average information worker. But it's not beyond "the ecosystem". I can definitely see third parties wanting to create "address parsing" transforms and the like. I think this is a great opportunity to add that kind of curated "store" concept to DE/SSIS, to take over from amateurish efforts like mine.
So when you see a demo of Data Explorer given, raise your hand as a data professional and ask the presenter about the enterprise story. That feedback will make it back to the product team, and make us all happier.

Data Flow Optimization - Collapse Sparse Buffers

2012-10-22T05:00:00.000-07:00

I'll warn you straight off that this is an advanced technique for a fairly small use case - it is NOT a "try this for all data flow performance problems" type of technique. (Much like the typical "adjust your buffer size" useless advice.) I ran into errors and warnings in my data flow about "A buffer failed while allocating X bytes" followed by "The system reports Y percent memory load...". The second error message itself reports very little memory load, and took some digging to get an idea as to what was going on. If you find yourself in this position, AND have a data flow similar to the one I describe, perhaps this can help.

How Small A Use Case Is It?

For myself - pretty darn small. I can only assume it's also fairly "edge" in general out there as well. In my specific situation, I'm attempting to load 22 million rows into a fact table. At the point in the data flow where this problem crops up, the row is 88 bytes wide (not counting overhead). I'm running this on my laptop with 8GB of RAM, over 4GB "free". My data flow should only take about 2GB of RAM to process (again - not counting overhead, and being VERY simplistic). I should have enough headroom.
However, I am doing something fairly interesting in the data flow that I've described in a partially-finished blog entry that's been unfinished for months and months. I'm allocating a total quantity over thousands of rows, and want to make sure I have no rounding errors in the allocation. In other words, I'm splitting a total dollar amount amongst a lot of rows according to a proportional formula, and I want to make sure it all adds back up to the original amount.
I'll leave it to that unfinished blog post I'll publish in the future to describe in detail how and why I go about doing it the way I do. But I have to describe it briefly to demonstrate the issue. It involves using a conditional split to funnel off a small percentage of rows (about 3%) from the main flow. The intent is to add/subtract whatever "remainder" or rounding error that occurred in the allocation calculation(s) to be adjusted on those rows. It also involves a Sort, Aggregate, and Merge Join - a lot of blocking and partially blocking components - just the kind of thing that usually clogs up memory - but doesn't in the way you'd think in this case.

More Diagnostic Details

Well - the errors you (and I) get seem to indicate that SSIS is failing for no reason. Here are verbatim errors from my output window:

Error: 0xC0047012 at Calculate Facts and Record Years: A buffer failed while allocating 79576 bytes.
Error: 0xC0047011 at Calculate Facts and Record Years: The system reports 68 percent memory load. There are 8577282048 bytes of physical memory with 2730938368 bytes free. There are 8796092891136 bytes of virtual memory with 8790742302720 bytes free. The paging file has 9099673600 bytes with 11153408 bytes free.

"68%" usage on an 8GB system, and it's complaining about not having enough memory? Seriously? 2.7GB physical memory "free", and there's still a buffer allocation failure?
I'm definitely no expert on analyzing what's going on, but I did break out the few tools I fumble around with. First, Task Manager. When I watch Task Manager's processes tab during a BIDS execution, I see that DTSDebugHost is (of course) doing all the work. The default columns shown in Task Manager include a "Memory" column - but it never gets very large - not even close to the 2GB I think should see if my pipeline is stuffed with 22 million rows. If you run the second tool, "perfmon /res" (I highly recommend it!) and watch memory usage as the data flow runs, you'll see a TON of "free memory" - even as SSIS (and probably windows itself, with little popup messageboxes like at the top of this article) is telling you that you're running out of memory. A little discomforting, to say the least...

But on that same Perfmon screen, look over to the right-hand side at the graphs. One of them shows the "Commit Charge" - and you'll see this one climbing steadily until it hits 100%. Then (sometimes) all hell breaks loose and your flow stops in its tracks with what looks like a bogus memory error.

The problem with the Task Manager memory number (and the "free" numbers seen in Perfmon) seems to be that this "memory" column isn't exactly the best one to be looking at. It's for the process' "Private Working Set", which (if I understand correctly) is the memory that the process is actively using exclusively. Taking the hint from Perfmon's graphs, the memory that really counts in this case is the "Commit Charge." This appears to be memory that the process has allocated to it, perhaps that it has already used, but hasn't been released (by Windows) back to the big usable pool yet.

If you change the columns you're looking at in Task Manager to add the "Memory (Commit Charge)" one - you'll now see a more accurate picture that matches up with the complaints you're getting from Windows and SSIS about memory pressure. In my flow execution, the Private Working Set doesn't exceed 500MB, but the Commit Charge climbs above 4GB!

What I Believe Is Happening

There's got to be a reason for this "excessive" commit charge, while actual memory "usage" is quite low, doesn't there? I can't explain it exactly, but my hunch is that it's due to memory fragmentation. Your package may not be actively holding on to that many bytes in memory, but if those bytes are sparsely used in a larger block of memory, then Windows may not be able to reclaim it for other processes (or itself).
It was that thought of "sparse usage" that made me look more closely at the Conditional Split I was using. For those of you that understand the Data Flow a little deeper than most - and you should if you read my blog - the Conditional Split doesn't make copies of data, or move data into two separate buffer stacks. It handles "splits" much more efficiently than that. Well, I should say efficiently with respect to CPU cycles and memory copying... because in our case, this efficiency is part of the problem. The Conditional Split adds a column to your data flow that only the data flow engine can see. (You can't - don't try.) Simplistically, this column is filled with the name of the output that row is supposed to flow out of. Components attached to Conditional Split outputs get ALL the buffers with ALL the rows that entered the Conditional Split - but they only operate on those rows with the right output indicator in that special hidden column.
The net effect of this behaviour, in combination with how the Sort component works, is an accumulation in memory of a set of very sparse data flow buffers. In my flow, one of my Conditional Split outputs - the one with 97% of the rows - is processed in a part of the flow where they get "consumed" by a Merge Join. The buffers that contain those rows ought to get released from memory, because those rows are no longer used. Except... for the 3% that went out the other output from the Conditional Split.
The 3% flow gets "consumed" by a Sort component. Except that I don't really think they do (right away). I believe that the Sort simply queues up the buffers, waiting for all of them to be given to it. It's doing what it's been designed to do - acting like a fully blocking component that needs to see all the buffers/rows before it emits any. However, the Sort is acting particularly poorly (in this case) because it's keeping the incoming rows inside the incoming buffers. Normally this isn't bad, and would actually be very smart. In this edge case of ours, it would make more sense to copy the incoming rows to another internal memory structure to release the 97% of rows that are "empty" in those buffers. But in normal cases, where you're sorting all (or almost all of) the rows inside an incoming buffer, it would be a waste of CPU and memory to make a copy of the incoming rows... that you're just going to discard anyway once you emit the sorted rows.
Bottom line - the Sort ends up holding on to (almost) EVERY ONE of my sparsely populated buffers. Buffers sized to hold 22 million rows, but only holding 1 million rows marked for its use by the Conditional Split.

The Solution - Defragment Your Buffers!

For those of you that scrolled to the bottom to get the TL;DR solution... you shouldn't have, because this probably won't work for you. It'll only work for those people that understand the problem they're having.
That said, it's fairly harmless to try this solution out. But do record baseline performance metrics on what your data flow's memory, CPU, disk, and time usage looks like so that you can determine if there's an improvement or not.
How do you "defragment" those sparsely populated buffers? Use a Union All.
The Union All is another asynchronous component just like the Sort - but it doesn't block like the Sort does. The Sort component won't process any inbound row until it has accumulated all of them. The Union All processes every incoming row as soon as it sees it... it just doesn't emit output buffers until it has full ones (or it's reached the end of the data).
We're going to use that behaviour to our advantage here. If you simply drop a Union All in your data flow between your Conditional Split and Sort, you'll be telling SSIS to behave in a much better manner for your (and my) unusual data flow. The "sparse" buffers that get emitted by the Conditional Split will get "consumed" by the Union All. (In actuality, I believe those rows get marked as "dead" by the data flow engine, and when all rows in a buffer are marked "dead", the engine can dispose of the buffer.) New buffers emitted by the Union All are dense, requiring much less "commit charge" to hold.
It worked wonders for me on this particular data flow. My Commit Charge was only a few hundred KB higher than my Private Working Set for the package execution, and I received no memory warnings or errors on multiple executions. If you know you're manufacturing "sparse" buffers in your Data Flow - a Union All might be able to help you out.

SSIS Raw File Viewer (2008/2008R2)

2012-10-15T05:00:00.000-07:00

Back in 2007, Simon Sabin published a file viewer for SSIS RAW files. Of course, that was for Integration Services 2005. Simon Trigona, one of my colleagues at Moulding and Millwork, recently built up a new 2008/2008R2 RAW file viewer from source that was published on the MSDN SSIS forums. He's built a CodePlex project for it, so you can see the internals and modify it as you see fit.
Of course, I don't know if this will work for SSIS 2012 - my bet is no... I haven't yet had the time to migrate, but hopefully that'll come soon. When it does, we'll be making any necessary adjustments.
Thanks to Simon Sabin and Mitulkumar Brahmbhatt.

The Only Thing Constant is Change

2012-09-12T06:00:00.000-07:00

The season is about to change for all of us, but this past Summer has been quite an eventful one for me. I won't bore you all with the details, but there are a few things I think will be of interest to readers here.
Scott Stauffer, whom I lured to come work with me full-time at Moulding and Millwork couldn't bear my authoritarian rule, and is back on the freelance circuit helping more people than just me. (I jest about the authoritarian comment - I'm sure I'm not that bad to work with, although I am sure our organization was too slow for Scott's faster pace.) Good wishes to Scott - I'll still see him very often, as I'll be one of his clients now. Hey! No Dilbert-esque "rehired at a higher rate as a contractor" comments, please ;)
Due in no small part to Scott's efforts, we now have a true Data Warehouse team and program here at M&M. That's even better than a "project" that implies some kind of time limit, which would have been better than the skunkworks guerilla work that I'd been pursuing before Scott arrived. I must say that the conventional wisdom about getting Data Warehousing going in an organization is completely correct in my case as well - if you don't have an executive sponsor that's fully committed, you're going to have a tough road to hoe.
All of the above activities, plus a LOT more internal projects here at work have cut down significantly on my output here at the blog, as well as at the MSDN forums. I'm lucky I was able to keep up some community work by presenting at DevTeach and New York's SQL Saturday. However, I do believe I've gotten on top of managing this change, so things should get closer to the "old normal." Here's hoping the reduced focus I felt I've placed on the community doesn't hurt my chances of getting renewed as a SQL MVP this October too badly. I really do appreciate the opportunity to communicate directly to the product team, and once I get deep into SSIS 2012 and related products, I'm sure I'll be wasting a bunch of their time!
I still really enjoy writing here, on the forums, and speaking to user groups even though I've been relatively quiet of late. I suppose that if priorities didn't ebb and flow, life would be quite stale. Now that I think I've figured out how to organize myself somewhat, I'll be back at it right here - and hopefully find time to complete version 2 of my BC Ferries app for Windows Phone (and Windows 8) as well.

Reading Excel into SSIS via OpenXML Scripting

2012-07-31T06:53:00.000-07:00

Reading Excel data isn't easy. It should be, but there's a lot of unfortunate barriers in the way that I've discussed before. They boil down to one main reason: Excel wasn't created for IT, it was created for business users. We're quite obviously an afterthought. That said, we're much more capable (technically) than business users, so even if the product wasn't designed with us in mind, we do have some options to bend it to our will.
OpenXML Revealed
One of those options is using the OpenXML API. Excel has supported OpenXML since Excel 2007 changed file formats from xls to xlsx. The extra "x" at the end of the file extension refers to the "XML" in OpenXML. The other Office 2007 and later app file formats are similar - they're all OpenXML too. The big secret about these file formats is that they're mostly not binary - they're plain-text XML... but compressed. To see this in action, take any xlsx (or other Office 2007 or later file), and change the file extension to .zip - then open it. Viola, you can now see a whole folder structure with a bunch of xml files in it. Careful what you touch - they're all inter-related.
What's the API do?
How is the OpenXML API different from the "regular" Excel COM API? In one big way: the COM API actually starts an instance of Excel and talks to it to read and write to Excel files. Instead of doing that, the OpenXML API understands the file formats, and allows you to read and write to the file directly, without knowing how to render, calculate, or otherwise "run" an Excel spreadsheet.
Of course, I'm not the first one to discover the OpenXML API... there are (of course) the Office-focused geeks that developed it, use it, live it, and breathe it. However, much of what they seem interested in is creating Excel files from applications, rather than consuming them. Documentation and examples of how to read SpreadsheetML (the spreadsheet "flavour" of OpenXML) is pretty damn poor.
So What's an SSIS Geek To Do?
Take matters into their own hands, that's what! I worked on this for a while, because I'm sure I'm not alone in having Excel hold a huge amount of organizational data. This critical information ranges from customer metadata (customer groupings and other attributes) to external economic indicators (housing starts, GDP, ...).
I've worked up a fairly robust script that reads named columns from a named table in an Excel spreadsheet, and maps them to the columns defined in the script editor. I've developed it such that I can copy and paste the vast majority of the script code - everything except the column mapping. Sooner or later I might turn it into a custom component... but maybe you can do that for me (wink, wink).
Here's The Code... With Training!
First, install the OpenXML API. Second, configure your script to output the columns (with data types) you want to read from the Excel table. Third, add a reference to "DocumentFormat.OpenXML" in your Script. Fourth, paste the code (you should probably only paste the contents of the class, not replace the entire class definition - I've included it here so you have context). Last, but not least, modify the first method in the script to set up your column mapping.
If you want some hands-on training, come to SQLSaturday #158 New York. I've got a session where we'll talk about what makes Excel so hard to use, and why using OpenXML is a pretty good solution.


/* Microsoft SQL Server Integration Services Script Component
*  Write scripts using Microsoft Visual C# 2008.
*  ScriptMain is the entry point class of the script.*/
using System;using System.Collections.Generic;using System.Data;using System.Linq;using Microsoft.SqlServer.Dts.Pipeline.Wrapper;using Microsoft.SqlServer.Dts.Runtime.Wrapper;using DocumentFormat.OpenXml.Packaging;using DocumentFormat.OpenXml.Spreadsheet;using DocumentFormat.OpenXml;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]public class ScriptMain : UserComponent
{
    #region Script Setup
    // Ensure you've set up output columns and supplied an Excel Connection Manager in the Script Editor UI
    /// <summary>
    /// Set the following variable value to the name of the table in Excel.  To find or set the name of the table in Excel,
    /// go to the Design ribbon in the Table Tools group.  The table name is shown on the left side.
    /// </summary>
    private static readonly string ExcelTableName = "CompanyDefs";
    /// <summary>
    /// Set this variable to true if you want copious reporting done (for debugging)
    /// </summary>
    private static readonly bool VerboseLogging = false;
    /// <summary>
    /// Fill this method with calls to MapColumn to map Excel column names to SSIS output columns, and provide data types.
    /// </summary>
    private void MapColumns()
    {
        // sample:
        // this.MapColumn("Excel Column Header", "SSIS Column Name", typeof(string));
        this.MapColumn("Sample Excel Column", "SSIS Column", typeof(string), true);    }
    #endregion

    #region Code You Don't Touch
    // The following code is configured based on the information you supplied in the section above,
    // and what columns and connection in the Script Editor UI.
    private const string SCRIPT_NAME = "OpenXML API Script Source for SpreadsheetML";
    private const string LAST_UPDATED = "2012-06-06 23:14";
    private static bool __script_last_updated_logged = false;
    private static IDTSComponentMetaData100 __metadata;
    /// <summary>
    /// The list of Excel to SSIS column maps
    /// </summary>
    private readonly List<ColumnMapping> _columnMappings = new List<ColumnMapping>();
    #region CLASS: ColumnMapping
    private class ColumnMapping
    {
        #region Property Setting Delegates
        public delegate void NullSetter(bool isNull);
        public delegate void StringSetter(string value);
        public delegate void Int32Setter(Int32 value);
        public delegate void DateTimeSetter(DateTime value);
        public delegate void BooleanSetter(bool value);
        //ADD_DATATYPES_HERE
        #endregion

        #region Private Variables
        private readonly string _excelColumnName;
        private int _excelColumnOffset;
        private readonly string _ssisColumnName;
        private readonly System.Type _dataType;
        private readonly bool _treatBlanksAsNulls;
        #endregion

        public NullSetteer SetNull;
        public StringSetter SetString;
        public Int32Setter SetInt;
        public DateTimeSetter SetDateTime;
        public BooleanSetter SetBoolean;
        //ADD_DATATYPES_HERE

        #region Constructor
        public ColumnMapping(string excelColumnName, string ssisColumnName, System.Type dataType, bool treatBlanksAsNulls)
        {
            this._excelColumnName = excelColumnName;
            this._excelColumnOffset = -1;
            this._ssisColumnName = ssisColumnName;
            this._dataType = dataType;
            this._treatBlanksAsNulls = treatBlanksAsNulls;
        }
        #endregion

        #region Public Properties
        public System.Type DataType
        {
            get { return this._dataType; }
        }

        public string ExcelColumnName
        {
            get { return this._excelColumnName; }
        }

        public string SSISColumnName
        {
            get { return this._ssisColumnName; }
        }

        public int ExcelColumnOffset
        {
            get { return this._excelColumnOffset; }
            set { this._excelColumnOffset = value; }
        }

        public bool ExcelColumnFound
        {
            get { return (this._excelColumnOffset >= 0); }
        }

        public bool TreatBlanksAsNulls
        {
            get { return this._treatBlanksAsNulls; }
        }
        #endregion

        #region SSIS Buffer Setter
        public void SetSSISBuffer(string value)
        {
            #region String
            if (this._dataType == typeof(string))
            {
                try
                {
                    this.SetString(value);
                }
                #region catch...
                catch (Exception ex)
                {
                    ReportError("Error encountered setting SSIS column '" + this._ssisColumnName + "' with string value '" + value + "': " + ex.Message, true);
                }
                #endregion
                VerboseLog("Set SSIS column '" + this._ssisColumnName + "' with string value '" + value + "'");
            }
            #endregion
            #region Int
            else if (this._dataType == typeof(int))
            {
                int intValue = 0;
                try
                {
                    intValue = Convert.ToInt32(value);
                }
                #region catch...
                catch (Exception ex)
                {
                    ReportError("Error encountered converting Excel column '" + this._excelColumnName + "' value '" + value + "' to integer: " + ex.Message, true);
                }
                #endregion
                try
                {
                    this.SetInt(intValue);
                }
                #region catch...
                catch (Exception ex)
                {
                    ReportError("Error encountered setting SSIS column '" + this._ssisColumnName + "' with int value '" + intValue.ToString() + "': " + ex.Message, true);
                }
                #endregion
                VerboseLog("Set SSIS column '" + this._ssisColumnName + "' with int value '" + intValue.ToString() + "'");
            }
            #endregion
            #region DateTime
            else if (this._dataType == typeof(DateTime))
            {
                DateTime dateValue = new DateTime(1900, 1, 1);
                try
                {
                    dateValue = dateValue.AddDays(Convert.ToDouble(value) - 2);
                }
                #region catch...
                catch (Exception ex)
                {
                    ReportError("Error encountered converting Excel column '" + this._excelColumnName + "' value '" + value + "' to DateTime: " + ex.Message, true);
                }
                #endregion
                try
                {
                    this.SetDateTime(dateValue);
                }
                #region catch...
                catch (Exception ex)
                {
                    ReportError("Error encountered setting SSIS column '" + this._ssisColumnName + "' with DateTime value '" + dateValue.ToString() + "': " + ex.Message, true);
                }
                #endregion
                VerboseLog("Set SSIS column '" + this._ssisColumnName + "' with DateTime value '" + dateValue.ToString("yyyy-MM-dd hh:mm:ss") + "'");
            }
            #endregion
            #region Boolean
            else if (this._dataType == typeof(bool))
            {
                bool boolValue = false;
                try
                {
                    if ((value.ToUpper().Trim() == "YES")
                        || (value.ToUpper().Trim() == "Y")
                        || (value.ToUpper().Trim() == "TRUE"))
                    {
                        boolValue = true;
                    }
                    else if ((value.ToUpper().Trim() == "NO")
                        || (value.ToUpper().Trim() == "N")
                        || (value.ToUpper().Trim() == "FALSE"))
                    {
                        boolValue = false;
                    }
                    else
                    {
                        ReportError("Invalid boolean value in column '" + this._excelColumnName + "': '" + value + "'", true);
                    }
                }
                #region catch...
                catch (Exception ex)
                {
                    ReportError("Error encountered converting Excel column '" + this._excelColumnName + "' value '" + value + "' to boolean: " + ex.Message, true);
                }
                #endregion
                try
                {
                    this.SetBoolean(boolValue);
                }
                #region catch...
                catch (Exception ex)
                {
                    ReportError("Error encountered setting SSIS column '" + this._ssisColumnName + "' with boolean value '" + boolValue.ToString() + "': " + ex.Message, true);
                }
                #endregion
                VerboseLog("Set SSIS column '" + this._ssisColumnName + "' with Boolean value '" + boolValue.ToString() + "'");
            }
            #endregion
            //ADD_DATATYPES_HERE
            else
            {
                ReportUnhandledDataTypeError(this._dataType);
            }
        }
        #endregion
    }
    #endregion

    #region Sets up map from Excel column to an SSIS column
    private void MapColumn(string excelColumnName, string ssisColumnName, System.Type dataType, bool treatBlanksAsNulls)
    {
        string methodName = "set_" + ssisColumnName.Replace(" ", "");
        VerboseLog("Creating " + dataType.ToString() + " mapping from '" + excelColumnName + "' to '" + ssisColumnName + "' via " + methodName);
        ColumnMapping mapping = new ColumnMapping(excelColumnName, ssisColumnName, dataType, treatBlanksAsNulls);
        #region Code to create delegates I'd have liked to have inside the ColumnMapping class itself if I could pass Output0Buffer...
        mapping.SetNull = (ColumnMapping.NullSetter)Delegate.CreateDelegate(typeof(ColumnMapping.NullSetter), Output0Buffer, methodName + "_IsNull");
        if (dataType == typeof(string))
        {
            mapping.SetString = (ColumnMapping.StringSetter)Delegate.CreateDelegate(typeof(ColumnMapping.StringSetter), Output0Buffer, methodName);
        }
        else if (dataType == typeof(int))
        {
            mapping.SetInt = (ColumnMapping.Int32Setter)Delegate.CreateDelegate(typeof(ColumnMapping.Int32Setter), Output0Buffer, methodName);
        }
        else if (dataType == typeof(DateTime))
        {
            mapping.SetDateTime = (ColumnMapping.DateTimeSetter)Delegate.CreateDelegate(typeof(ColumnMapping.DateTimeSetter), Output0Buffer, methodName);
        }
        else if (dataType == typeof(bool))
        {
            mapping.SetBoolean = (ColumnMapping.BooleanSetter)Delegate.CreateDelegate(typeof(ColumnMapping.BooleanSetter), Output0Buffer, methodName);
        }
        //ADD_DATATYPES_HERE
        else
        {
            ReportUnhandledDataTypeError(dataType);
        }
        this._columnMappings.Add(mapping);
        #endregion
    }
    #endregion

    #region CreateNewOutputRows - the only method called from SSIS, this is the "entry point"
    public override void CreateNewOutputRows()
    {
        #region Set up verbose logging
        __metadata = ComponentMetaData;
        #endregion

        // See http://blogs.msdn.com/b/brian_jones/archive/2008/11/10/reading-data-from-spreadsheetml.aspx
        // http://openxmldeveloper.org/discussions/formats/f/14/p/5029/157797.aspx
        // http://blogs.msdn.com/b/ericwhite/archive/2010/07/21/table-markup-in-open-xml-spreadsheetml.aspx

        #region Configure Column Mapping
        this.MapColumns();
        if (this._columnMappings.Count != ComponentMetaData.OutputCollection[0].OutputColumnCollection.Count)
        {
            string message = this._columnMappings.Count.ToString() + " column relationships have been set up, but the Script Source has "
                + ComponentMetaData.OutputCollection[0].OutputColumnCollection.Count.ToString() + " output columns defined.";
            ReportError(message, true);
            throw new ArgumentException(message);
        }
        VerboseLog(this._columnMappings.Count.ToString() + " column mappings defined.");
        #endregion

        #region Extract Excel file name from connection manager
        string workbookFileName = null;
        SpreadsheetDocument document = null;
        try
        {
            VerboseLog("Extracting Excel file name from connection manager.");
            string connectionString = ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager.ConnectionString;
            string[] connectionStringParts = connectionString.Split(';');
            foreach (string connectionStringPart in connectionStringParts)
            {
                string[] pair = connectionStringPart.Split('=');
                if (pair[0] == "Data Source")
                {
                    workbookFileName = pair[1];
                    VerboseLog("File name of '" + workbookFileName + "' identified in connection manager.");
                    break;
                }
            }
        }
        #region catch ...
        catch (Exception ex)
        {
            ReportError("Unable to parse connection string: " + ex.Message, true);
        }
        #endregion
        #endregion
        #region Opening Excel file
        if (workbookFileName != null)
        {
            try
            {
                VerboseLog("Attempting to open Excel file.");
                document = SpreadsheetDocument.Open(workbookFileName, false);
            }
            #region catch ...
            catch (Exception ex)
            {
                ReportError("Unable to open '" + workbookFileName + "': " + ex.Message, true);
            }
            #endregion
        }
        VerboseLog("Excel file opened.");
        #endregion
        try
        {
            WorkbookPart workbook = document.WorkbookPart;
            SharedStringTablePart sharedStringTablePart = workbook.SharedStringTablePart;
            #region Unused code for finding ranges
            //ComponentMetaData.FireInformation(0, "", "Got WorkbookPart", "", 0, ref fireAgain);
            //#region Look at Ranges
            //bool foundRange = false;
            //RangeDef rangeDef = new RangeDef();
            //foreach (DefinedName name in workbook.Workbook.GetFirstChild<DefinedNames>())
            //{
            //    ComponentMetaData.FireInformation(0, "", "Looking at defined name '" + name.Name + "'", "", 0, ref fireAgain);
            //    if (name.Name == this._rangeName)
            //    {
            //        ComponentMetaData.FireInformation(0, "", "Saving def", "", 0, ref fireAgain);
            //        rangeDef.Name = name.Name;
            //        string reference = name.InnerText;
            //        ComponentMetaData.FireInformation(0, "", "  reference: " + reference, "", 0, ref fireAgain);
            //        rangeDef.Sheet = reference.Split('!')[0].Trim('\'');
            //        string[] rangeArray = reference.Split('!')[1].Split('$');
            //        rangeDef.StartCol = rangeArray[1];
            //        rangeDef.StartRow = rangeArray[2].TrimEnd(':');
            //        rangeDef.EndCol = rangeArray[3];
            //        rangeDef.EndRow = rangeArray[4];
            //        foundRange = true;
            //        break;
            //    }
            //}
            //ComponentMetaData.FireInformation(0, "", "Done looking for defined names", "", 0, ref fireAgain);
            //if (foundRange)
            //{
            //    string rangeID = workbook.Workbook.Descendants<Sheet>().Where(r => r.Name.Equals(rangeDef.Sheet)).First().Id;
            //    ComponentMetaData.FireInformation(0, "", "Got rangeID " + rangeID, "", 0, ref fireAgain);
            //    WorksheetPart range = (WorksheetPart)workbook.GetPartById(rangeID);
            //    ComponentMetaData.FireInformation(0, "", "Got Range", "", 0, ref fireAgain);
            //}
            //#endregion
            #endregion
            #region Iterate over sheets to find table
            VerboseLog("Searching sheets for table '" + ExcelTableName + "'.");
            Table table = null;
            Worksheet worksheet = null;
            foreach (Sheet sheet in workbook.Workbook.Sheets)
            {
                VerboseLog("Examining sheet '" + sheet.Name + "'.");
                WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheet.Id);
                foreach (TableDefinitionPart tableDefinitionPart in worksheetPart.TableDefinitionParts)
                {
                    VerboseLog("Sheet contains table '" + tableDefinitionPart.Table.DisplayName + "'.");
                    if (tableDefinitionPart.Table.DisplayName == ExcelTableName)
                    {
                        worksheet = worksheetPart.Worksheet;
                        table = tableDefinitionPart.Table;
                        VerboseLog("Sheet and table found.");
                        break;
                    }
                }
                if (table != null)
                {
                    break;
                }
            }
            #endregion
            if (table == null)
            {
                ReportError("Table '" + ExcelTableName + "' wasn't found in '" + workbookFileName + "'.", true);
            }
            else
            {
                string firstColumnHeader = "";
                #region Find Excel Column Offsets
                VerboseLog("Collecting column offsets for mapped columns.");
                int columnIndex = 1;
                foreach (TableColumn tableColumn in table.TableColumns)
                {
                    if (columnIndex == 1)
                    {
                        firstColumnHeader = tableColumn.Name;
                    }
                    foreach (ColumnMapping columnRelationship in this._columnMappings)
                    {
                        if (tableColumn.Name == columnRelationship.ExcelColumnName)
                        {
                            VerboseLog("Found Excel column " + tableColumn.Name + " at offset " + columnIndex.ToString());
                            columnRelationship.ExcelColumnOffset = columnIndex;
                            break;
                        }
                    }
                    columnIndex++;
                }
                #region Throw an error if not all columns were found
                foreach (ColumnMapping columnRelationship in this._columnMappings)
                {
                    if (!columnRelationship.ExcelColumnFound)
                    {
                        string message = "Unable to locate column '" + columnRelationship.ExcelColumnName + "' in table '" + ExcelTableName + "'.";
                        ReportError(message, true);
                    }
                }
                #endregion
                #endregion
                #region Read spreadsheet data into SSIS output buffer
                SheetData sheetData = worksheet.GetFirstChild<SheetData>();
                IEnumerable<Row> rows = sheetData.Elements<Row>();
                #region Find First Row
                UInt32 firstRow = 0;
                VerboseLog("Finding first row of table.");
                foreach (Row row in rows)
                {
                    foreach (Cell cell in row.Elements<Cell>())
                    {
                        if (this.CellReferenceToCoordinates(cell.CellReference)[0] == 1)
                        {
                            if (this.GetCellValue(cell, sharedStringTablePart) == firstColumnHeader)
                            {
                                firstRow = row.RowIndex + 1;
                            }
                        }
                    }
                }
                VerboseLog("First row of table is on row " + firstRow.ToString() + ".");
                #endregion
                VerboseLog("Preparing to read " + rows.Count<Row>().ToString() + " table rows from Excel.");
                foreach (Row row in rows)
                {
                    VerboseLog("Reading row " + row.RowIndex.ToString() + ".");
                    if (row.RowIndex < firstRow)
                    {
                        VerboseLog("Skipping non-table or header row.");
                    }
                    else
                    {
                        VerboseLog("Reading data row " + (row.RowIndex - 1).ToString() + ".");
                        bool rowAdded = false;
                        foreach (Cell cell in row.Elements<Cell>())
                        {
                            foreach (ColumnMapping columnRelationship in this._columnMappings)
                            {
                                if (this.CellReferenceToCoordinates(cell.CellReference)[0] == columnRelationship.ExcelColumnOffset)
                                {
                                    string cellValue = this.GetCellValue(cell, sharedStringTablePart);
                                    if ((cellValue == null)
                                        || ((cellValue == "") &amp;&amp; columnRelationship.TreatBlanksAsNulls))
                                    {
                                        // do nothing
                                    }
                                    else
                                    {
                                        if (!rowAdded)
                                        {
                                            Output0Buffer.AddRow();
                                            rowAdded = true;
                                        }
                                        VerboseLog("Excel column '" + columnRelationship.ExcelColumnName + "' contains '" + cellValue + "'.");
                                        columnRelationship.SetSSISBuffer(this.GetCellValue(cell, sharedStringTablePart));
                                    }
                                }
                            }
                        }
                    }
                }
                #endregion
            }
        }
        #region catch ...
        catch (Exception ex)
        {
            ReportError("Unable to open Excel file using OpenXML API: " + ex.Message, true);
        }
        #endregion
        VerboseLog("Closing Excel file");
        document.Close();
    }
    #endregion

    #region Helper functions to change Excel ranges to numeric coordinates/offsets
    private int[] RangeReferenceToCoordinates(string rangeReference)
    {
        int[] coordinates = new int[4];

        string[] cellReferences = rangeReference.Split(':');
        int[] startCellReference = this.CellReferenceToCoordinates(cellReferences[0]);
        int[] endCellReference = this.CellReferenceToCoordinates(cellReferences[1]);

        coordinates[0] = startCellReference[0];
        coordinates[1] = startCellReference[1];
        coordinates[2] = endCellReference[0];
        coordinates[3] = endCellReference[1];

        return coordinates;
    }

    private int[] CellReferenceToCoordinates(string cellReference)
    {
        //bool fireAgain = true;
    t[] coordinates = new int[2];

        //ComponentMetaData.FireInformation(0, "", "CellRef: [" + cellReference + "]", "", 0, ref fireAgain);
        int index;
        cellReference = cellReference.Replace("$", "").Trim();
        #region Collect column letters -> column
        string column = "";
        for (index = 0; index < cellReference.Length; index++)
        {
            if ("ABCDEFGHIJKLMNOPQRSTUVWXYZ".Contains(cellReference[index]))
            {
                column += cellReference[index];
            }
            else
            {
                break;
            }
        }
        #endregion
        //ComponentMetaData.FireInformation(0, "", "column: [" + column + "]", "", 0, ref fireAgain);
        #region Convert column into number -> coordinates[0]
        for (int power = 0; power < index; power++)
        {
            coordinates[0] += ("ABCDEFGHIJKLMNOPQRSTUVWXYZ".IndexOf(column[column.Length - power - 1]) + 1) * (int)Math.Pow(26, power);
        }
        #endregion
        //ComponentMetaData.FireInformation(0, "", "col coords: [" + coordinates[0].ToString() + "]", "", 0, ref fireAgain);
        #region Convert row into number -> coordinates[1]
        coordinates[1] = Convert.ToInt32(cellReference.Substring(index));
        #endregion
        //ComponentMetaData.FireInformation(0, "", "row coords: [" + coordinates[1].ToString() + "]", "", 0, ref fireAgain);

        return coordinates;
    }
    #endregion

    #region Helper function to read Excel cell values
    private string GetCellValue(Cell cell, SharedStringTablePart sharedStringTablePart)
    {
        if (cell.ChildElements.Count == 0)
        {
            return null;
        }
        if ((cell.DataType != null) &amp;&amp; (cell.DataType == CellValues.SharedString))
        {
            return sharedStringTablePart.SharedStringTable.ChildElements[Int32.Parse(cell.CellValue.InnerText)].InnerText;
        }
        else
        {
            return cell.CellValue.InnerText;
        }
    }
    #endregion

    #region Logging functions
    private static void VerboseLog(string message)
    {
        bool pbFireAgain = true;

        if (!__script_last_updated_logged)
        {
            __metadata.FireInformation(0, __metadata.Name, "Excel OpenXML API Source Script (" + LAST_UPDATED + ") running.", "", 0, ref pbFireAgain);
            __script_last_updated_logged = true;
        }
        if (VerboseLogging)
        {
            __metadata.FireInformation(0, __metadata.Name, message, "", 0, ref pbFireAgain);
        }
    }

    private static void ReportError(string message, bool fatal)
    {
        bool pbCancel;
        __metadata.FireError(0, __metadata.Name, message, "", 0, out pbCancel);
        if (fatal)
        {
            throw new ApplicationException(SCRIPT_NAME + " had a fatal error.");
        }
    }

    private static void ReportUnhandledDataTypeError(System.Type dataType)
    {
        // Need to add a Data Type?  Search for ADD_DATATYPES_HERE
        ReportError("This script can't handle " + dataType.ToString() + " types.", true);
        throw new ArgumentException("This script can't handle " + dataType.ToString() + " types.");
    }
    #endregion
    #endregion
}

Presenting at Vancouver DevTeach

2012-05-29T06:00:00.000-07:00

This post should have been made a couple months ago so that my legions of rabid fans could have lined up to... yeah, OK - I'm not fooling anybody, I know. But for those that are signed up to DevTeach in Vancouver, you should definitely put my sessions in your schedule. I mean, if you read my blog, I know you're interested in Integration Services, and that's what I'll be talking about.

I'll give you the first day to get warmed up to the conference experience. I'll be hanging around from midday onwards, picking up a few sessions and hanging around in between poking at my computer.
But first thing on the second day, come bright-eyed and bushy-tailed to hear some good solutions for common application pattern problems in SSIS. I mentioned in the abstract that we'll look at processing files in date order, and upsert patterns. I've got five other topics I can cover - flat files with headers and footers, deduplicating data, emailing resultsets, calculating running totals, inserting parent/child rows, and zipping files. The good part is that the audience decides what we spend our time on! If nobody there cares about some of those topics, we don't have to waste time listening to it.
On the third conference day, if you touch Excel spreadsheets with Integration Services, you can't miss my afternoon session. Scratch that - if you use SSIS, you obviously have spreadsheets as a source. Be there. There aren't any foolproof techniques, but there's a great big bag of excellent ones that should get you through the minefield of Excel flexibility and user inconsistency. The big reveal will be a sample of using OpenXML to read data from a spreadsheet. I don't think I'm a fan of OpenXML yet - but it'll be hard to resist if it continues to work this well. (I'll be blogging about that soon.)
DevTeach is a very cozy and informal conference - I have no doubt that those of you who are attending will love it. Don't hesitate to take advantage of that environment - the speakers are all willing to sit down with you about any topic. I know I am, so if you have an interesting SSIS problem, or just want to moan about the weather, post a comment here, or mail me at <myfullnamewithnospaces>@live.ca. Look forward to seeing you all there.

Basic SSIS Equivalents to T-SQL's LIKE

2012-05-22T05:00:00.000-07:00

There are some things that we do in one SQL Server tool, but can't seem to figure out how to do in another. One of those for you might be the LIKE keyword in T-SQL. LIKE is fairly powerful in T-SQL - it does rudimentary regular expressions. How do you do that in SSIS? Well, you can't get too fancy, but here are some basics.
Columns That Contain a Value
This applies to cases where your T-SQL query might include a clause like this:

col LIKE '%value%'

The SSIS expression equivalent involves the function FINDSTRING. FINDSTRING looks in any string expression (including a column) for an occurrence of a particular substring, and which occurrence to look for.
The equivalent expression in SSIS to the example above is:

FINDSTRING(col, "value", 1) > 0

Columns That Start With a Value
This applies to cases where your T-SQL query might include a clause like this (where X is a number):

col LIKE 'value%'

LEFT(col, X) = 'value'

This is a fairly rudimentary case - and there are multiple methods for developing an equivalent SSIS expression with FINDSTRING, or LEFT, such as:

FINDSTRING(col, "value", 1) == 1

LEFT(col, X) == "value"

Columns That End With a Value
This one's a little trickier (in SSIS2008R2 and below), and has prompted a few questions on the MSDN SSIS forums. The T-SQL case would look something like:

RIGHT(col, X) = 'value'

There's a LEFT in SSIS' expression language... but no RIGHT prior to SSIS 2012, so what can you do? How about using REVERSE and LEFT, like this:

REVERSE(LEFT(REVERSE(col), X)) == "value"

More Complicated Demands?
T-SQL's LIKE can service more complicated pattern searching - look for future posts where I'll try to investigate some. If you have any particular requests, post a comment...

The Effective Permissions Tool is Handy

2012-05-14T05:00:00.000-07:00

An recurring issue with Integration Services packages that read or write to file systems is permissions issues. It's quite difficult to have a package run properly when it can't see the source files, or can't write results to a particular network share. But it's insanely hard to get a package working if you don't think permissions are set up properly but your sysadmins do. I'll rehash what many of you already know (the typical problem and typical solution) in order to provide some background, then I'll call out a tool I've used when that just doesn't work... and you need to persuade your sysadmins to look a little harder.
SQL Agent Jobs Fail...
These errors typically surface when the job is put into production as a SQL Agent Job Step, where it's executing under an account you didn't develop it with. You'll typically get an error in the job history that looks like this:

Executed as user: DOMAIN\MACHINE$. Microsoft (R) SQL Server Execute Package Utility Version 10.0.5500.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 3:42:58 PM Error: 2012-05-07 15:42:58.76 Code: 0xC001401E Source: Flat File Read Connection manager "Test" Description: The file name "\\Share\Folder\Testing.txt" specified in the connection was not valid. End Error

Your first reaction is "I can see that file - of course it's there!"
It's not hard to overlook the fact that the account the job is executing under is the SQL Agent account - not yours, and it needs the same permissions your account did in order to do this job. (Well - hopefully not exactly the same permissions - just those few that are absolutely necessary.)
... Because They Don't Have Permission ...
You can see that in the first part of the message - "Executed as user:" (I've replaced real data with fake names to protect the innocent.) The message tells me that the job is executing under a system account (my fictitious computer is named "machine"). The folder "Folder" on the network share "Share" doesn't permit that account access, so the job fails.
You have a few options to deal with this - some are better than others:

You can grant access to \\Share\Folder to DOMAIN\MACHINE.
You can create a Proxy to execute the package with your account credentials (since you have permissions).
You can create a new domain account specifically for this purpose, grant it appropriate rights to the network share, create a Proxy for it, then configure the job to use that Proxy.

I always choose (3). No, I don't ask our sysadmins to create a new account for every Agent job. I create "service accounts" that I can use to manage distinct "systems." I've got one that handles ETL work for my data warehouse. I've got another that handles some import work for a third-party system. Each of them is used in a few places, but the key is that their permissions are very low (no interactive login, etc) and they only get rights as necessary to complete their tasks.
However you do it, managing proxies usually gets you to your goal... until it doesn't.
... Even After They Should!?
I've run into scenarios where I'd swear a certain account ought to have access to a resource, and yet it doesn't seem to. In the case of file systems there's one particular tool that has saved my addled brain a couple times. After all, computers are dumb machines (no offense intended) and only do what we've told them to do. So if they're refusing access... it must be because we've instructed them to do so.
Given that we've narrowed the problem down to our own inability to configure security, we need to confirm that diagnosis, and possibly figure out why. The tool I'm talking about is the Effective Permissions tab of the security settings dialog. To get there, find the file system location or object you're trying to access and open its properties. Switch to the security tab (which you swear shows what it should). Hit the Advanced button. This (permissions) view alone may highlight the mistake you've made - you may think certain accounts inherit things in a certain manner, but don't. You may think certain permissions apply further down the folder tree, but don't.
In case you're still not enlightened as to why things aren't working, and need proof one way or the other that permissions really are the issue, click over to the Effective Permissions tab. Type or select the account your proxy is configured for, and you'll see exactly how the operating system resolves the permissions.
If you set up the security settings, I hope this cuts down on the possible problems and highlights just a few. If you've asked someone else to set up permissions, this tool should show you whether the job was done correctly or not.

How To Use T-SQL TIMESTAMP (ROWVERSION) in SSIS

2012-05-08T05:00:00.000-07:00

In some database designs, you may encounter SQL Server columns with a TIMESTAMP or ROWVERSION data type. What are these, and how do you deal with them in SQL Server Integration Services?
Time? Version?
First, let's talk semantics. There are several unfortunately bad aspects to these names. First, the name "TIMESTAMP" is deprecated - newer versions of SQL Server use the name ROWVERSION instead. I can imagine that there was confusion that the data type somehow represented an actual time value (it does not), or matched the ISO standard definiton of a TIMESTAMP data type (it does not). Unfortunately the ROWVERSION name doesn't describe it that well to me either, as there is no relation to the primary key, or how many updates have been made to a particular row.
A better description would probably be to call the data type SEQUENCE... but that's already a keyword used for another construct in SQL Server. A column with a TIMESTAMP or ROWVERSION data type is an auto-incrementing, unique value for the entire database. (Perhaps it's implemented internally as a SEQUENCE... ;) ) Another wrinkle is that this column gets updated whenever any data in the row is changed by an UPDATE. So it's almost like an IDENTITY column for inserts and updates.
What's it Used For?
As highlighted in the opening of the MSDN page for ROWVERSION, this data type is (allegedly) typically used to version-stamp table rows. In my (limited) experience, it can be equally (mis)used similarly to a GUID column - a unique identifier for a row in a table, or a hash-like "has this been modified" value.
However it's used in the construction of the table, it usually enters into the Integration Services world as a column you have to compare against another table's column - typically to detect changes.
Some database developers may take advantage of the documented (but not guaranteed in my reading) "incrementing" behaviour, and use it to help determine which row is the newest when comparing two rows.
Careful reading of the MSDN page also shows that duplicate rowversion values are possible if SELECT INTO statements are used improperly. Something to watch out for there.
How Does it Appear to SSIS?
When you extract data from a table that has a ROWVERSION column, SSIS will see it as a DT_BYTES column of 8 bytes. This makes sense, as the MSDN page describes it as semantically equivalent to a binary(8) or varbinary(8).
Since it's a byte stream, that greatly limits what you'll be able to do with this data in SSIS - but then, the intent of this data type isn't to contain any more data than some kind of version stamp. Your most likely action inside SSIS is to compare this value with the value from another table to see if they're different...
Performing Comparisons
If you try the straightforward comparison in a Conditional Split or Derived Column... you'll get a syntax error. Despite claims to the contrary, SSIS can't use any comparison operators on DT_BYTES columns. (Except the ISNULL function.)

What to do? Convert the DT_BYTES to a data type that SSIS can compare - DT_WSTR. There's a tricky spot here though - you can't convert the DT_BYTES(8) to a DT_WSTR(8). The conversion code sometimes generates more character bytes - so double the space to a DT_WSTR(16), and you should be off to the races.

Quick Reference: How Do I Read an Excel XSLM (Macro-Enabled) File in SSIS?

2012-04-09T05:30:00.000-07:00

Excel 2010 (and presumably the next version of Excel) have taken some security precautions with regards to macros in Excel files. If a spreadsheet has macros in it, you can't save it as an XLSX. Excel forces you to save it with an extension of XLSM, indicating that the file has macros in it. The assumption being, I suppose, to make it a little harder to sneak a malicious macro under a user's nose when they're not expecting one.
The Problem
This causes problems with SQL Server Integration Services 2005 and 2008, because the developers didn't take macro-enabled files into consideration when they designed the Excel Connection Manager. If you attempt to use a macro-enabled Excel file name in the Connection Manager, it will report that the path "contains invalid Excel File" (bad English too). It recommends you identify a file with an XLS or XLSX extension.
The Solution
Point the Connection Manager to a valid Excel XLSX file - any one will do, and save it.
Edit the Connection Manager properties (press F4) and change the ExcelFilePath property to your actual macro-enabled Excel file. Consider SSIS fooled.

Quick Tip: Custom Component Performance

2012-04-02T05:30:00.000-07:00

As regular readers will know, I've got a few custom components for SQL Server Integration Services under my belt, some of which I've published at CodePlex. As I was working with one of the components I haven't published, I got a little frustrated with the length of time it was taking the UI to appear when I'd double-clicked it, as well as the length of time it took to return to the design surface after accepting my changes.
After a little investigation, the excessive time could be traced to a single type of operation that occurs several times in this component inside the Validate and ReinitializeMetaData methods. It's quite a common thing to do - so if you happen to have created any components for SSIS, I suggest you read on .
What's Slow?

The performance drag? Iterating over IDTSInputColumnCollections and IDTSOutputColumnCollections. Probably a no-brainer for you knowledgeable COM interop experts out there - but iterating over those collections is slow.

Where's the Nitrous?
To avoid those iterations, I constructed my own "temporary" collection(s) with the same elements to iterate over instead. In some cases I used a Hashtable (keyed by column name or lineage ID), and in some cases a simple List was just fine. I still incurred one performance hit in building my temporary collection - because I had to iterate over the COM collection once. After that, smooth sailing and a huge performance increase.
Warning...
Do be a little careful though! Creating a temporary collection to "mirror" the built-in one is perfectly safe when in the Validation context, because nothing is permitted to change. However, you have to watch your own code and the "staleness" of your collection in the ReinitializeMetaData context. Both you and SSIS can be altering the input and output collections at this time. So if you're playing with the UsageType on inputs, or adding or removing columns from asynchronous outputs, you'll have to make similar adjustments to your temporary collections or they'll get out of sync.

It's Quiet... Too Quiet

2012-03-26T05:30:00.000-07:00

It's been a LONG time since I've posted something - but that trend that will change shortly! I've been working on a number of special projects within Moulding and Millwork - some related to data gathering, curating, and analysis, and some less so. It's taken away some of the time I'd previously had available for working on the MSDN forums, my blog, speaking, and working on custom objects for SSIS.
The workload of those special projects has diminished a little, and now that SQL 2012 is here, I've got to get back to some professional development in my own bailiwick. I'll also make sure to prod Scott Stauffer to make some time to share his thoughts via his blog as well. It's not his normal outlet (you can usually find him cultivating user groups and events) but he's said he wants to do a little more, so I'll make that happen.
Presentations
First and foremost, I'll be getting ready for some speaking engagements this year:

April 19th, I'll be speaking at the PASS Data Architecture VC. This is a free online presentation of your options within SSIS for processing dimensions.
At the end of May, SQLTeach returns to Vancouver! I'll be presenting two sessions very much related to Integration Services: Common Application Patterns, and Integrating Spreadsheets with Enterprise Data. If you're near Vancouver then, I highly recommend the conference. It's got a great panel of speakers, and it might be your first opportunity to get some insight into SQL 2012 and what it might mean to your environment.

On top of those two, there are a couple other events this year I'll be submitting talks to - I'll keep you posted on those.
Blogging
Even though I haven't published any posts in the last few months, I have a few (42) started. I'll find the time to polish some off and get them out the pipe. In particular, I finally cracked the OpenXML API to get at Excel spreadsheets without using the fickle OLE API. It's no less complex, but at least it works consistently and reliably.
I've also got a few design patterns and other thoughts to share - look for those soon.
Custom Objects
I'm primarily talking about the Dimension Merge custom component here - but this applies equally to some of the other tasks and components I've developed over the past couple years. I've made some small changes that haven't been put up to CodePlex, and there is a short list of needed adjustments that I'll be getting to. Top of that list is to get the code for all of them prepped for SQL 2012 and SSDT. There are some specific enhancements that could help performance out on some of them that I'd like to take advantage of.
Q&A
Last, but definitely not least... this was actually the first thing I "got back into" as my workload returned somewhat to regular levels... trolling the MSDN forums. If you've got a question about Integration Services, it's by far the best location on the internet to find an answer. I agree, it may not have the best forum software, but it definitely has the critical mass of traffic to make turnaround on questions quick, and of reasonable quality.

PASS Community Summit 2011 Day 3 Keynote

2011-10-14T15:32:00.000-07:00

Dr. DeWitt has spoken to the PASS Summit for the past two years – turning what was otherwise a very forgettable “day 3” keynote slot into a must-see event for anyone going to the Summit.
The past two years Dr. DeWitt took us through the internals of columnstore databases and query optimization. They were fantastically content-rich and dug right to the bottom of the technology. On top of explaining the mechanics of the query optimizer and theory of column-based storage, Dr. DeWitt demonstrated understandable applications of the change in technology to common queries and functions we all enjoy today in SQL Server. He's also a pretty funny presenter.
That brings us to today and the changing world of data. We’re going to get the concept of “big data” funneled into our brains faster than we can process it – but that’s part of why I’m here. I hope to capture some of the content that comes flying at us this morning about Hadoop and distributed data processing. But my other job is to comment on how this movement will affect our industry going forward, changing your data, your job, your server architecture, and even SQL Server internals. I have no doubt that I’ll only scratch the surface of what Dr. DeWitt conveys this morning – and for that reason alone, I recommend you don’t miss his keynote (if they can get him) next year at the PASS Community Summit.
According to Dr. DeWitt, big data is a large collection of data – petabytes – on a large collection of machines. To others, “big data” means NoSQL, but that's not a requirement to Dr. DeWitt. For some sophisticated commentary on Dr. DeWitt’s presentation (and some inspired discussion), I expect to see something on Jeremiah Peschka’s blog – I’d encourage you to go read it if you end up interested in this type of technology.
The data explosion is largely due to automation, a sea change in collecting data. In the past, it was all manually entered, but now cell phones collect location data automatically, clickstreams rule advertising, and on and on. The “old guard” (and Dr. DeWitt freely considers himself part of that) would handle these petabyte data warehouses on a parallel database system. The new guard wants to use NoSQL. In his observations, current implementations of NoSQL take about 5 times the server hardware as parallel technologies. For example, EBay stores 10PB on 256 nodes vs. Facebook’s 20PB on 2700 nodes, and Bing’s 150PB on 27,000 nodes! Of course, parallel DB technologies are typically (for good reason) delivered as appliances of high-quality hardware compared to NoSQL usage of commodity type (possibly-heterogeneous) servers. As with anything to do with databases - "it depends."
NoSQL isn’t all the same – it’s a big tent of technology – but there are a couple major types: Key/value stores (CouchBase, MongoDB, …) and Hadoop. The former are intended primarily for single-record updates/retrievals – you can think of them as the “OLTP NoSQL” technology. Hadoop is designed for processing massive data sets, and is roughly expressible as “data warehouse NoSQL.”

The claim that NoSQL works on unstructured data, and RDBMSs are for structured data is a misconception. NoSQL databases do have structure – it’s just that the structure is applied to the data later in the process than for your traditional RDBMS. Instead of the metadata being stored with the data – in fact being a requirement to actually store the data – NoSQL metadata is “applied” when the analysis function processes the data. I think of it as “compiled” vs. “interpreted” code.

Only eight years ago, Google started in on the big data problem in order to analyze clickstream data. Hadoop is an open-source interpretation of Google’s Map/Reduce+GFS system. The general structure has two parts – a fault-tolerant data storage layer (HDFS) and a fault tolerant processing layer.

The data storage layer provides fault tolerant storage using file redundancy across different nodes in the cluster of servers, protecting against individual server failure as well as entire racks failing. You can think of this like RAID – data is smartly distributed over the cluster. If one node fails, the data exists on other nodes, so it isn’t lost. The main difference here is that RAID only addresses single disk failures, whereas the HDFS protects against query, disk, server, switch, rack, and datacenter failures without necessarily having to restart or recover… using only one “redundancy” algorithm.

The layer that sits on top of the storage is the processing engine – Map/Reduce. Map/Reduce is best at performing aggregations, but it isn’t designed to do joins. Joins take a ton of code to achieve – four full screens of multiple “programs” compared to a quarter-screen of SQL (HiveQL). Dr. DeWitt compared performance of a few TPC-H “structured” queries between Hive and PDW – all showing significant wins by parallel database “old guard” technology. The reason is primarily due to “old technology” being intentionally designed to handle these specific type of queries – even though they’re aggregations that he’d just said perform typically better in NoSQL scenarios. Why? Because of the inherent optimizations possible when the data used is a TPC-H workload, which is already schema’d and loaded. If the data structure wasn’t known ahead of time, a parallel database would not be capable of servicing the query at all – until metadata was “applied” by performing ETL.

There still is a strong use case for NoSQL – and his reasoning follows how I think about the relationship between SSAS (cubes) and PowerPivot (tabular) analysis technologies. In Map/Reduce, the schema is stored in the application that’s doing the analysis itself – not within the data like in SQL. So it’s a lot “easier” and better to use when you’re doing exploratory analysis of data. Much like PowerPivot, you only need to define enough schema to get your particular job done – not enough schema to get any type of query done, or “explain” each piece of data in the set. But at some point, if your exploratory analysis finds value in the data, you’re going to want to move it into an RDBMS to get the query performance out of it, and have a consistent schema applied to avoid two different analysis applications interpreting the data in inconsistent ways. Currently, there are techniques and technologies for moving data between these platforms, but they can be pretty difficult and involved. One of those is called Sqoop.

Dr.DeWitt envisions a better way to share data between SQL and NoSQL worlds than Sqoop. He’s investigating the concept of an “Enterprise Data Manager” that can relate and use unstructured and structured data so that the appropriate engine can traverse and analyze whichever type of data it’s presented with.

A few ideas Dr. DeWitt had for this year that “didn’t make the cut”, but could be a topic for next year’s Summit:

What the “Cloud” means for databases
The impact of Solid State Drives and Phase Change Memory
... whatever you might want to see – send your ideas to PASS!

If you want more, visit David DeWitt and his team on the web, there are links to the PASS talks on the left side. My thanks go to Dr. DeWitt and the team that got him up on stage - I really hope they can do so again next year.

What I Learned at #SPC11

2011-10-07T06:00:00.000-07:00

This week, I attended the Microsoft SharePoint Conference 2011 (#SPC11) in Anaheim. Not my usual beat, to be sure, but our company is starting to use SharePoint more, and we have no internal resources trained on it. We have a "wild west" set of sites already, plan to add more for cross-functional project team sites, want to host BI solutions delivery, and we've got a project on the books to migrate an existing old-tech Intranet to SharePoint. Better late than never, we're taking this opportunity to learn a little more.

I'm A Newb Here

Overall, the conference was pretty good - I learned things I needed to know and got more comfortable with the usage, architecture, and management of SharePoint. Of course, having not known these things prior to our "wild west" deployment, I now know we did it all wrong. Some good news is that even though I may not yet know the "perfect" way to deploy SharePoint for our needs, I know what questions to ask, and generally how to "fix" our current deployment.

There was one more good opportunity I took advantage of at the conference. I found myself in a fairly typical position for other people: I'm an almost complete newbie at the technology, and I didn't know anyone in the community. That sounds eerily familiar to the situation that first-timers have at the SQL PASS Community Summit. I was in the same position with SharePoint that they'll be in with SQL Server. The difference is that I now know what I didn't when I was a first-timer at Summit. I now know a great deal about the technology, the community, and the conference itself. I'm participating as a "big brother" or "guide" for a gaggle of first-timers... Could I put my own "first-timer" experience at the SharePoint conference, contrast it with my "veteran" status at Summit, and help my first-timers get great value out of the Summit? You bet.

And It Wasn't A Great Experience

To start off, I'll describe my experience at SPC11 for you - the discomfort, unfamiliarity, and frustration of it all. Yes, I wasn't terribly comfortable at the conference. Despite what you may assume from reading here or meeting me in person, I'm an introvert. I only exhibit this extrovert type of behaviour when I'm around friends, or in an environment where I'm comfortable with my perceived "expertise." I didn't really want to start conversations with people I ate breakfast and lunch with in the convention dining hall. Saying more than hi - even to the bare extent of offering my name as an introduction was difficult. I forced myself to, but my reluctance was reinforced by the lack of responses I usually got. I hardly acknowledged people sitting next to me in sessions - I often sat two or three seats away from anyone else. Sound like your typical convention-goer? It does to me.

Add to that that I'm completely unfamiliar with the technology. How do I know what sessions to go to, or what vendors to talk to? I know that my organization has issues with SharePoint - not knowing how to use it, manage it, or architect it well. But is there a session that covers that? Not that I could find. One may argue that a convention isn't the place I should be going to find this stuff out... but here I am at SPC11 in exactly this boat, and I'm positive there are other wallflowers bailing water with me. I don't even know the terminology well enough to know where to start! From what I now know about SharePoint and language used to describe it, I missed some helpful sessions. Luckily they'll be online for me to review later. But I won't get the opportunity to talk to the speaker after the session, or meet others who were also up at the podium asking similar questions (making me feel better about being a dummy) or talk to other experts that were trolling the area, offering assistance. Opportunity definitely lost...

Being uncomfortable with the setting and unfamiliar with the technology made me frustrated. I'm not used to being in that position, and the frustration drove me to learn more so I didn't stay in that situation. Unfortunately, there's not much in the way of hand- or foot-holds to help me climb out of that pit... so I got more frustrated!

It wasn't a complete disaster, I did get a lot of useful information out of the conference. Thanks in no small part to Mahmood Jaffer - he helped fill in that basic knowledge I was missing. Yes - it only took one contact to "make things better." But the experience itself was illuminating to me as to what little things could have helped me get so much more out of it. I'm glad to say that PASS' programs - of which the first-timer program is an example - do help. But even though PASS is doing many things "right", I think they could do more, and relatively easily.

Necessity Is The Mother...

Here were my issues on being a first-time conference attendee at the SharePoint Conference:

I couldn't identify "experts". (Perhaps I should clarify that I'm equating "experts" with "people that will help me".) I think speakers had a different badge accent colour - but I'm not sure. Vendors definitely did. MVPs did not. No ribbons either. I spent the entire welcome reception in vain trying to find one - not even vendors knew who or where they were.
I didn't know my (independently reserved) hotel had a shuttle to the conference until I registered.
I didn't know my (independently reserved) hotel internet access was covered until I checked out. (I bought it anyway - had to in order to do office work, and was prepared to expense it.)
Apparently, there was a "birds of a feather" area. The only information I saw on it was on a room directory, but I didn't investigate further. I don't know what kind of "feathers" they were - industry or feature - and I didn't know when it was either.
Apparently, there were Hands-On labs available. Again, I only saw passing reference to rooms on signage, without any description of lab content in the conference registration package or emails. (They did promote certification exams though.)

I think it's also important to note I had the double-whammy of being a product newbie as well as a conference newbie. Here were my issues on being completely unfamiliar to the technology of SharePoint:

I didn't know the product features or purpose - not even enough to know what I didn't know.
I didn't know how important knowing certain things was. Was understanding claims-based security more important than knowing how to leverage content types?
I still don't even know what I don't know...

Even though I'm sure some of the above two lists was in the documentation or registration package, I was more interested in addressing other issues than in reading all that to pull out a nugget or two.

...Of Invention

I think the conference organizers could have helped me out with those problems by:

Delivering better preparatory email to new/all attendees. I was actually shocked by the grand total of two - yes TWO - emails from the conference organizers that only served to help me prepare logistically... minus key shuttle and hotel internet information.
Targeting emails by attendee "type". I don't want a ton of spam either. Separating communications for "newbs" versus "returnees" would help keep content from being inadvertently categorized as spam or irrelevant by the receiver.
Identifying experts. SPC11 didn't have the ribbons all us PASS vets know (and I hope you love as much as I now do). Yes, they had a "badge customization" booth you could go to after registration to "add bling" to your badge (their words, not mine) with fairly unhelpful ribbons like "Internet Sensation" and "Hire Me". (Really?) Summit's ribbons are better, but perhaps they aren't as helpful as they could be. Instead of a ribbon for "MVP" or "Speaker" (can't remember all the ones I've had), they should have one of the colours (say red) with technology words. Pasting a red "Clustering" ribbon on your badge would mean "I know clustering, and I can (and want to) answer your questions about it." SPC11 didn't put those "bling" ribbons in everyone's packet (nor would I want PASS to), they had a free-for-all booth to pick and apply your own. That would work for a set of "I'm here to help with" ribbons (borrowing Andy Leonard's trademark).

SPC11 could have helped a product ignoramus like me more easily by:

Having an experts area manned at all hours of the conference. Heck, "experts" is asking too much. "People with a passing familiarity" would do! I would have LOVED to have sat down with someone at the Welcome Reception who could have:

Found out what I knew about SharePoint and what I didn't
Found out what my learning goals were for the conference
Given those, helped me to identify appropriate sessions/events to attend

Having an "I'm new to SharePoint" track. I know that program selection is a hard job - one of the problems is having appropriately leveled content. If there was a dedicated "I'm new to <product>" track with ONLY 100-level sessions in it, I think a newbie would do quite well, thank you. Yes, as a SQL newb, I might only care about DBA type content, not BI, cloud, and/or modeling content that would (should) be delivered in such a track. But if the first session(s) in that track explained the big picture - the stack, the different tech, AND the typical job descriptions associated with SQL Server, I think that would help newbs a LOT. Follow that up with intro sessions to the sub-products and features. With that taken care of, the program committee could feel more freedom in loading up on 300/400 level sessions.
Alternatively, have an "I'm new to <product>" pre-con to cover that...
Pass out/win "I know <product> <feature>, thanks to <conference>" shirts with "Ask me about <feature>" on the back. Have them at the ATE booths for "experts" to give to attendees they think learned something about the subject... with a soft commitment from attendees to WEAR THE SHIRT during the conference and bring it next year.

Make Conferences Better!
The above lists are directed at the SharePoint Conference - but I hope PASS can take away some nuggets as well. I think most of those points could help make the first-timer and/or newbie experience so much better, and draw attendees into the awesome community that we have already. Since this has turned into an "open letter" to PASS (I'll be drawing their attention to it), I would also encourage members of the PASS board to attend a conference every year that they've never attended before - big, small, on a technology which they're completely new to, doesn't matter - for the express purpose of putting themselves in the shoes of those they want to serve.

A "New Guy" Joins Our Team

2011-10-03T10:57:00.000-07:00

It's a great day for me and Moulding and Millwork (the company I work for). A few weeks back, I let you all know that I'd been given the green light on finding someone to help me out with Data Warehousing. One of the first people I turned to for suggestions on finding someone was Scott Stauffer, the chapter leader of the Vancouver BI PASS chapter, PASS regional mentor, and SQLSaturday organizer. I was hoping Scott could help me find the right person out there to help grow and support the fact-based decision making engine I envisioned working at M&M. Scott has a ton of contacts from being a leader in the community, and having a busy consulting practice in the area for years and years.
Scott thought my news was great - he was really excited for me that what I've been working towards was looking like it was gaining a lot of traction. But then he surprised me. He didn't start by giving me a list of people he knew that would fit the position. Nor did he point me towards recruiters or job boards. The first thing Scott asked was if I thought he would fit the position. That totally threw me for a loop. I was expecting to have either a naive kid to train up completely, or a SSAS geek to learn from. It won't surprise you to find out that I was (after the shock) VERY interested in working out arrangements with Scott. (If you get an MVP interested in working with you... you find out a way to say yes.) On top of being a great community supporter and organizer - which only touches the tip of his interpersonal capabilities and vision for the big picture - Scott is an exceptional technical talent at the whole Microsoft BI stack. Yes, he'll claim that I've got him beat in Integration Services, but I actually don't think so. I'm a hacker (in the classical sense) in SSIS, and only have one "project" to claim on my resume. I've managed to milk that into an MVP award for the third year in a row now (yay!) which I just found out about this past weekend. But Scott has a list longer than my arm of successes in this space that I now get to draw on and use to help our IT department support our business with insightful data to help them make visionary decisions quickly and more confidently than ever before.

With that, I'll be accepting your congratulations to me for landing Scott as a partner in crime here at Moulding and Millwork. It's a real coup for me, and I'm pretty happy with myself at the moment. ;) I'll make sure his badge at PASS Summit is appropriately labeled with his new allegiance. Oh... and I guess you can send your congratulations to him on twitter or in person at the Summit. He'll have to get used to the good-natured ribbing I dole out - welcome to M&M Scott!

Parallelizing Slow Parts of the Data Flow - Part 1 - Preparation

2011-09-06T05:25:00.000-07:00

It's quite common to have parts of your Data Flow that are slow, and there are some techniques you can use to improve performance of those parts. One of them is to try to parallelize the slow operation - but that only works for operations that are parallelizable. Sort isn't one of them - sorry to say. And you really have to know how the data flow works to make sure you don't try fixing a problem that looks "serial" but is, in fact parallel - you'll only make things worse.
Identifying Parallelization Opportunities
So what scenarios does parallelization work for?
Positive Signs
Here are some characteristics that should identify a good candidate for parallelizing a portion of your data flow.

The operation applies independently to single rows, or to an easily identifiable subset of rows.
There's a known-slow resource in use that allows concurrent use (SQL Server, disk, web, ...)

Counter Indications
If your data flow has any of the following characteristics, it's not likely that parallelizing (alone) will help speed anything up.

The server's CPU is at or near capacity during the slow section of the data flow.
Any external resources (SQL Server, file system, network) the slow section of the data flow uses are already at or near capacity.
Some part of the slow section requires all rows at once - an aggregate or sort, for example.

Test, Backup, Change, Test, Backup, Change, ...
After applying a parallelization technique, you'll need to figure out if it's better, right? Then the first thing you have to do is measure the performance of your current design (obviously!). Make sure you measure a few things - time to completion, CPU, RAM, network, and disk utilizations. Run the test at least three times in a row to eliminate caching effects and get a good average.
We measure all those attributes so that we can see what we're trading speed for. The idea is to trade CPU utilization for speed - but we could have to pay RAM and I/O as well - to a degree you might not be comfortable with. That's why we keep an eye on those metrics.
Before you make any changes... make sure you back up your original! Your quest to improve performance using one technique might work somewhat, and you may want to try another technique. If you have a backup of your original package, it'll (probably) be easier to implement the second technique from there, while ensuring your actual logic remains consistent. Do also back up your attempts - you might try a few techniques but decide the first one worked "best".
Make your change to parallelize the package, then double-check the result is the same as your original. It doesn't do any good to make your data go faster if you ruin it in the process.
Be Prepared For Disappointment
Even if your slow section seems to fit the profile I outlined, there's no guarantee applying a parallelization technique will help. You may not be able to squeeze any more time out of the process due to some limiting factor you weren't aware of when you started. Or the trade of speed for some other resource just isn't something you're ready to make.
What follows will be posts on using the Balanced Data Distributor, then a "poor man's" BDD using existing components for those of you that can't install additional software (even when published by Microsoft).

Inserting Records AND Getting The Identity in SSIS - Part 2

2011-08-29T05:59:00.000-07:00

Since I posted a ways back on retrieving the identity value for inserts in SQL Server Integration Services, I've learned many things - one of which is an inline solution for that problem I didn't know back then. Check out that previous post for the business problem, then come back here for an "inline" solution using SSIS - without scripts - just regular SSIS tasks and components.
It's a Love/Hate Relationship
The OLE DB Command component in SSIS is a near-universally hated object due to it's inherent performance problems. But it's so hard to hate when it can actually get the job done when nobody else can.
Yes, our old nemesis is back to solve a problem for us... at the same price it's always asked. Do you want your packages to run fast, but be complicated? Or do you want them to run slow, but be easy to understand? Sadly, there is not much middle ground.
The Keys
It turns out to be pretty easy, and I should have got it into my thick skull before, from all the hints I'd read. A stored procedure with an OUTPUT parameter, and the OLE DB Command are the keys. For this example, assume we're using a table that has only two columns: ID and TEXTVALUE, with ID being the automatically generated identity column.
The Stored Procedure
Unfortunately, this doesn't use much of the GUI power of SSIS, it's old hand-coding. For our simple table, this would be the stored procedure definition:


CREATE PROCEDURE InsertData
  @textvalue AS CHAR(10),
  @id AS INT OUTPUTAS
INSERT INTO IdentityTable 
  (textvalue) 
  VALUES 
  (@textvalue)
SET @id = SCOPE_IDENTITY()

The SSIS Data Flow
Use a Derived Column to generate the ID column with a NULL(DT_I4) value in it. (We need somewhere to place our identity value in.)

Following that, we can work on the OLE DB Command component, using a statement like this:


EXEC InsertData ?, ? OUTPUT

Map the columns as you'd expect to map them.
If you'd like I've got an SSIS 2008 package showing this technique.
The Big Picture

Sure, it works - but here are the drawbacks:

It's going to perform badly. Horribly if the package is run on a different server from the database the stored procedure is on. I can't stress that enough. The OLE DB Command component is making a round-trip call to the database for each row. Even on the same machine, that's an expensive operation.
You're doing an RBAR (row by agonizing row) process which is much, much slower than a set-based process that SQL Server is more suited for. (SSIS is fine with RBAR, it's designed that way.) Neither SQL Server process nor the Integration Services runtime will be working very hard during this part of the data flow - I don't think even one core would get utilized fully due to the wait-states involved.
If you're interested, here's a sample package to show this process. It creates a database on your local machine, creates the sample table, generates one row, performs the insert (and shows the result to you), then tears the database down.
Can we mitigate this RBAR flaw? Stick around or subscribe your RSS reader to find out in an upcoming post.

Anyone Want to Work With Me?

2011-08-22T13:44:00.000-07:00

Let's see... my last post was almost a month ago, and it was a pretty small one. I've been busy!
But it's good to be busy, especially when the housing market has looked so bad in the past few years, and you work for a company that directly serves that industry. My guerilla war about data as (apparently) been successful enough that I'm now looking for someone to help us out, full time.
What Do I Want?
I want your job. Seriously. I'm of two minds on getting another body in here. On the one hand, it should make things happen faster - but on the other, that's work I won't get to do and things I won't get to learn. If I could clone myself, or get by with only a few hours sleep a night like Buck Woody, I'd do it. (If you know me personally, that last sentence is pretty funny.)
What's the Job?
I need some help working on our data warehouse. I feel pretty comfortable with the "first half" of the technical aspects of it - sourcing the data and transforming it into an appropriate dimensional model inside a relational store. What I don't know well is the "last half" - working Analysis Services, a reporting tool (RS, Crescent, Tableau, QlikView, ...), and SharePoint. We have a small team of hardware/OS guys, but they don't know this stuff either, and don't have a massive budget to address our coding inefficiencies.
This Isn't a Backroom Job
If you're not very comfortable talking to "suits," you won't enjoy this job. Without a doubt, you'll be working with C-level executives and VPs from time to time. Even though you may feel most at home in a cave gently washed by LED backlighting, you'll need fantastic communication skills in email, phone, and face-to-face situations all over North America. It won't be the majority of your work, but you'll have to be comfortable with teaching "end users," evangelizing our capabilities, and listening very hard for opportunities and difficulties with data.
Self-Starters Only
Another reason I'm hesitant to move in this direction is that I loathe having to monitor other people... in part because I get distracted by work myself. If you need constant feeding, at some point you'll starve to death because I've unintentionally ignored you. You need to find your own work and check in with others from time to time to make sure we're all part of the same pack, pulling in the same direction.
I'm looking for a flexible mind, a problem solver, and a committed learner. Someone who isn't a 9 to 5er, but understands that's when they'll get called for information. You will also have to feed my desire to learn from you. I want to understand the technology so we can offer the guys who do real work at this company the data and insights they need to keep us on top. I'll reciprocate gladly - anything you want to know about anything you think I know.
Benefits Are Great
I'm not going to go into a lot of details here - because there's only one that should get me the applicants I want, the rest should take care of itself. I like working here because I get to do what I love, and work with people that like what they do. If you're passionate about fact-based decision making, and want to help provide it to a company that's waking up to how valuable that can be, contact me.

Quick Hit: Using XLSM Files With SSIS

2011-07-27T05:14:00.000-07:00

If you've tried to use an Excel 2010 XLSM file - a "macro-enabled" spreadsheet - with Integration Services, you'll have seen the incorrect (and badly worded) message that the "File path contains invalid Excel file. Please provide file with .xls or .xlsx extension." It looks like the SSIS team just didn't know that Excel makes other flavours of files.
How Do You Get Around This Limitation?

Create an Excel Connection Manager to refer to ANY other spreadsheet with a "valid" XLS or XLSX extension. (You don't have to save your XLSM file "as" an XLSX.)
Select the Excel Connection Manager in your Connection Managers pane.
Open the Properties Window (F4).
Change the "Server Name" property to the full pathname of your XLSM file.

Done!

PASS Summit 2011 Content - It's Your Choice

2011-07-19T05:47:00.000-07:00

If you're reading this post and attending the PASS Summit 2011 in Seattle, you absoutely should vote for two things with regards to the Summit: Dr. DeWitt's keynote subject, and the Community Sessions.
Dr. DeWitt's Keynote
I've been to the Summit for the past two years (I think... it's hazy) and one of the absolute highlights has been Dr. DeWitt's presentations. 2009 was on columnar database technology, 2010 was about query optimization. Believe me - he does not dumb things down, and he's not from marketing. There's a reason everyone raves about his presentations - he talks to geeks the way we want to be talked to.
Just like last year, PASS is asking you to help select Dr. DeWitt's topic. The theme this year seems to be parallelism technology - big data or big iron. Would you like the doctor to explain Hadoop and distributed data processing, or describe the coming impact of the increasing number of CPU cores and I/O changes? If you're a DBA, I can see you being interested in the Moore's Law presentation. But since I'm closer to the DBD demographic, I've voted for the Hadoop talk.
Best quote from Dr. DeWitt last year (I'm paraphrasing): "Guys that drop out of query optimizing because it's too hard... go build rockets for NASA."
Community Sessions
Apparently, the PASS Summit Program Committee is taking a day off, because PASS is asking for our help to round out the Summit schedule. (I kid - the PC did a great job this year and deserve a day off... even though I know they're not taking one.) From various sources, the Program Committee has selected 20 candidate sessions that are competing for five slots. Here are my choices, based solely on what I need to learn and want to see:
Robert Cain on Project Juneau - The team I'm with needs to understand databases better. It's really hard to help them get there when the code they write is so very disconnected from the database. As I understand it, Juneau is a step towards integrating code and the database better - which would give me better tools to encourage using better code and a real database.
Wendy Pastrick on Visualizing Indexes - My understanding of index internals is woefully deficient. I certainly think I know the basics, but I could really use more.
Chris Webb on SSAS Security - This one is probably over my head at the moment - but I know it's inevitable that I'll be called upon to deliver it. My org is presently just too paranoid (IMO) about spilling too many secrets to parallel lines of business.
John Welch on Advanced DW Scenarios - Some of what I want to be able to deliver in my DW seems to require what John's talking about here... but I don't know enough to say for sure, that's why I have to go.
Jen Stirrup on Data Visualizations - This topic is both fascinating to me and severely frustrating. I yearn for the ability to get rid of the complex reams of tabular reporting that require significant mental effort to decipher. I've done my best with Excel 2007, but I need to expand my toolset to accomplish transforming my business' expectations.
Vote Now
If you're attending the Summit, please vote for the keynote and session schedule you'd like to see. You've only got until the 20th of July. Please don't make it a popularity contest about the speaker - because that means I'll lose :) Yes, I happen to be listed twice! I have a session on complex SSIS executions and another on processing slowly changing dimensions in the running. If straightforward SQL Agent Jobs can't solve your SSIS execution scenario, I've got some techniques to help. If John Welch's presentation on advanced DW scenarios is a little too advanced, maybe mine fits in your ballpark a little better.
Whatever your interests, please do pick some. It's a shame that so many great sessions are in competition with each other - but I'll be happy to be a loser if it means you get sessions that will do you some good!

Presenting Upserts to PASS AppDev VC

2011-07-18T05:52:00.000-07:00

Hopefully this crowd knows a little about what they're getting into! John Jakubowski (blog|twitter) has graciously asked me to present on upsert strategies with SSIS. What are "upserts"? A not-so-fancy mashup of the words "update" and "insert".
If you struggle with incremental table loading performance - updating rows that already exist, and inserting rows that didn't - then you might want to drop by.
When?
July 26th, 4pmGMT/12pm ET/9am PT
Where?
Go to the PASS AppDev website, and find the LiveMeeting link.
What?
The full abstract:
One of the most sought after techniques in Integration Services is how to perform "upserts" - propagating changes from one system to another that may require new records to be inserted, or existing records to be updated. Integration Services can't accomplish this with a single click, but it does contain all the tools required to construct packages that will achieve this goal. The task of synchronizing data will be broken down into two parts: detecting changes, and updating or inserting. Refinements to use of the Lookup and alternatives to the OLE DB Command transform will be explored.

What is Pipeline Backpressure?

2011-07-07T06:26:00.000-07:00

First - I'll get the "what's a pipeline got to do with Integration Services" question out of the way. The "pipeline" I'm referring to here is the word commonly used to describe the flow of data in the Data Flow Task. That term doesn't seem to show up in any of the MSDN documentation, but the Microsoft bloggers just can't seem to talk enough about it. I suppose it's an internal term that's leaked out into general usage.

The SQL Server Integration Services' Data Flow Task reads data from one or more sources, flows that data through this "pipeline" where some parts of it will get transformed (changed), and then sends the data to one or more destinations. The choice of the word "pipeline" will tend to get people thinking of a liquid in a pipe - and that's an apt metaphor for the phenomenon of backpressure. The same kind of action takes place in SSIS.

Fluid Backpressure
When talking about a liquid, backpressure refers to the resistance that the fluid encounters flowing through a pipe. When the fluid encounters a constricted section of the pipe, it can't flow as fast through that section. But since (most) fluids are incompressible and most pipes are rigid, the reduction in flow rate at this constricted point means that the flow is "backed up" all the way to the start of the pipe. If a kink is stopping the flow of water near the end of the hose, the tap isn't able to inject any more water into the hose - there's simply no space to put it. The rate at which you can inject water into the hose is directly related to two things: how constricted the smallest part of the hose is, and how much the hose can "expand" prior to the blockage. (If the hose can inflate like a balloon, you can still pump water in.)
Data Flow Backpressure
SSIS's Data Flow Task has very similar constraints. The source - or "tap" - wants to inject data into the pipeline as fast as it can. But that data has to flow through the components - the "pipe" - in order to reach the destination - the "end" of the hose. Somewhat like a garden hose, the data flow pipeline can stretch to allow the source to inject more data into the pipeline than is ultimately getting through to the end. But there's a limit, of course, just like with a hose. If it's stretched too much, it will burst.
In SSIS's case, since it's a "fictitious" hose, the nice engineers that built it were able to set limits on how much of a balloon you're allowed to turn your hose into. It's the RAM limit of the process and/or machine you're running your package on. In most cases, taking advantage of SSIS' "stretchy hose" is a very bad thing - you'll easily consume all the available memory on your machine, and start spilling into virtual memory... and that's slow disk!
Backpressure In Action
Here's an SSIS package that demonstrates backpressure quite well.

You can build your own quite easily from what I'll describe here, in case you don't have SSIS 2008. The first task in it is a Data Flow Task that looks like this:

The first component hooks up to your local instance's master database, fetching a cross-joined resultset from sysobjects. (It's the volume of rows that matter here - not the content.) The second component is one of my Data Flow wiretap scripts that will dump timing information to the logging stream that we'll be able to watch in the Output window. The last component is a script destination. Normally, I'd just use a Union All or Derived Column to cap the flow, but those result in a lot of "unused column" warnings that would clutter up the logging stream.

As you can see below, running this flow results in something like this pretty quickly. In my case, under 1.5 seconds, just about as quick as SQL Server can deliver the rows to SSIS. (So fast I didn't bother to capture an "in-flight" picture.)

The next Data Flow Task in the package shows how an "impeded" data flow will behave. In this flow, there's one more script component in there.

The component inserted at the third position is intended to mimic a transformation that takes effort to perform. This is the equivalent of an uncached Lookup, OLE DB Command, Merge Join, etc... What does this look like at runtime? Well, no problem taking a screen capture in mid-execution on this one...

How does this image show the effects of backpressure? It was taken over 20 seconds after the data flow started. Let me explain in more detail.

Our first "free-flowing" data flow loaded the data into SSIS' pipeline in under 1.5 seconds. The second "impeded" data flow hasn't even finished loading the 100K rows after 20 seconds... why not? Because the "sleep" transform is slowing things down. So why isn't the source simply reading as fast as it can, and piling up the data at the slow transform's doorstep? Because if it did that, it would consume memory. The developers of SSIS thought that consuming memory in this situation would be completely unnecessary if they could just tell the source to "slow down" to a manageable level... so they did.

Any "execution tree" (there's another bit of SSIS internals jargon) will experience this backpressure - forcing every component to operate no faster than the slowest. One of the key parts of managing the performance of your packages is understanding pipeline backpressure. It can mislead you about what's performing slowly in your flow, causing you to spend time and effort where it will have no effect. Tweaking the DefaultBufferSize and DefaultBufferMaxRows properties can change some of the effects of backpressure - but doing so only delays the inevitable with sufficiently large data sets, and typically offers little meaningful benefit. The real key is understanding your data flow's behaviour through decomposition.

I have one more example data flow for you in the package, where I add a sort component to the middle of the flow. How will this affect the flow of data? The sleep component will definitely cause the end-to-end flow to be slow, but will the source stream quickly like the "free-flowing" example, or will it be throttled like the "impeded" flow? I'll leave that up to you to explain...

Code Snippet for an SSIS DataFlow Performance Wiretap

2011-06-20T05:56:00.000-07:00

I was recently tweaking a few Data Flows, and settled on a very small, but very useful script to help record execution timing inside the flow.

Decomposition Is Time Consuming

You may know that I do recommend the decomposition technique to help diagnose performance problems in SSIS data flows. That's the technique where you start recording execution times using fewer and fewer components in the data flow in order to get a differential execution time for particular components. Even though it's not perfect, it does account for misleading effects of the pipeline backpressure mechanism. (What I suggest later does not.)

Unfortunately, performing a decomposition analysis can take quite a while. It is time consuming to replace the destination component with a row count, run the flow, record the results, and continue up the flow.
Adding a Performance Wiretap is Easy
Especially when the code is completely copy-and-paste! I couldn't measure any performance impact of including this script code, and didn't expect any. It doesn't use any columns in the data flow, and the operations it does do are very lightweight. You don't even have to change anything in the script itself - the events that are logged include the name you give the component in the data flow (which must be unique) so you can easily identify which of several wiretaps is reporting information.



public class ScriptMain : UserComponent

{

  #region Statistic collectors

  private DateTime _preExecuteTime;

  private DateTime _firstRowTime;

  private DateTime _lastRowTime;

  private int _numRows;

  #endregion



  #region PreExecute

  // This method is called before any rows are read from any sources

  // We reset all statistics here

  public override void PreExecute()

  {

    base.PreExecute();

    this._firstRowTime = DateTime.MinValue;

    this._lastRowTime = DateTime.MinValue;

    this._numRows = 0;

  }

  #endregion



  #region ProcessInput

  // This is NOT a regular method override you normally see in a script component

  // This override is called once for each input buffer (set of rows) that

  // are passed into this script component. We count rows here, and record

  // first and last "row seen times".

  public override void ProcessInput(int InputID, Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer Buffer)

  {

    base.ProcessInput(InputID, Buffer);

    #region Record the time the first row was seen

    if (this._firstRowTime == DateTime.MinValue)

    {

      this._firstRowTime = DateTime.Now;

    }

    #endregion

    #region If this buffer has any rows (the last buffer typically does NOT), record the time

    if (Buffer.RowCount > 0)

    {

      this._lastRowTime = DateTime.Now;

    }

    #endregion

    #region Add the number of rows in this buffer to the row count statistic

    this._numRows += Buffer.RowCount;

    #endregion

  }

  #endregion



  #region PostExecute

  public override void PostExecute()

  {

    bool fireAgain = true;

    base.PostExecute();

    #region Output statistics to package logging, as Information events

    ComponentMetaData.FireInformation(0, "",

      ComponentMetaData.Name + " first row seen at " + this._firstRowTime.ToString("hh:mm:ss.fff"),

      "", 0, ref fireAgain);

    ComponentMetaData.FireInformation(0, "",

      ComponentMetaData.Name + " last row seen at " + this._lastRowTime.ToString("hh:mm:ss.fff"),

      "", 0, ref fireAgain);

    TimeSpan duration = (this._lastRowTime - this._firstRowTime);

    ComponentMetaData.FireInformation(0, "",

      ComponentMetaData.Name + " watched " + this._numRows.ToString("###,###,##0") + " rows pass by in " + duration.TotalMilliseconds.ToString() + "ms",

      "", 0, ref fireAgain);

    #endregion

  }

  #endregion

}

So what does it do? It records the time the first row passes it, the number of rows that go by, and the time the last row passes it.
How Can This Help Diagnose Performance?
If you want to know which section of your Data Flow takes the longest duration - place some of these scripts at various places in the flow. After you run your Data Flow, you'll see how long each section takes - keepin gin mind their execution times will overlap each other. (If you use two scripts, the second script's "first row" may very well be seen before the "last row" passes through the first script.)
If you're wondering what kind of impact a certain change has in your Data Flow - place this script before and after the affected area. Run your flow before you make the change, and again after. The results will show you how much impact your change had (but remember it won't account for hidden backpressure effects!)
If you're wondering which of several "branches" of a Data Flow that are combined using a Union All is the slowest, you can use these wiretaps to instrument each one.
Once again - please don't think this is a replacement for correct use of the decomposition technique. Component performance in the Data Flow depends heavily on other components coming before and after the area you're interested in - and even flows occurring in parallel in the same (or other) Data Flow.

How Do You Deal with Excel in SSIS?

2011-06-06T09:16:00.000-07:00

This Friday June 10th, I'll be presenting to the Vancouver BI PASS chapter about Excel. I've got a TON of problems, solutions, resources and tips to compile together - Excel can be a real finicky beast to deal with from an automation standpoint.

Given all the problems with connectivity, data types, formatting, and so on... why don't ETL developers just proclaim that they won't take data in the form of Excel spreadsheets? Because then we'd have no work to do! It's pretty uncommon to hear of a data integration project that doesn't involve Excel in part of it. The source OLTP system probably isn't a set of spreadsheets - but quite a lot of supporting metadata, reference information, or budgets and forecasts come from the ubiquitous Excel. It's the business' most comfortable vehicle for storing data - I constantly hear it referred to as a "database" (by business folks). Well, we can't snigger from on high in our ivory tower about how rediculous it is to call Excel spreadsheets databases - we have to deal with this data!

The presentation will be in the Microsoft offices in downtown Vancouver - 1111 West Georgia, 11th floor at 10:30 PDT. Come early to chat - I'll be there around 10am, depending on how long a conference call goes. I hope to see you there if you're a local - but if not, you can still tune in virtually. Scott will have the RSVP and LiveMeeting links up on the Vancouver BI PASS website shortly.
And for those that are really paying attention - if there's a specific issue you're interested in solving between SSIS and Excel, ask away during the presentation - or be nice to me and leave a comment here prior to Friday so I come prepared!
UPDATE 2011-06-22: The recording, samples, and slide deck are now available.

Use Connections Properly in an SSIS Script Task

2011-05-31T05:47:00.000-07:00

There are lots of scenarios in SSIS where you'd want to use a connection within a Script Task. Unfortunately, the closest help documentation - the comments inside the Script Task itself - give you exceptionally poor advice:
To use the connections collection use something like the following:
ConnectionManager cm = Dts.Connections.Add("OLEDB");
cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";
Why is this advice bad? Let me count the ways:

The connection string is hardcoded inside the script.
The connection can't be used with Package Configurations.
The connection string (and name) imply that OLEDB connections can be used.
As presented, the connection won't participate in MSDTC transactions.

(And seriously, "use something like the following"? Hey - I write the vague blog posts here. They're supposed to be writing documentation. ;) )
The poor advice boils down to the fact that it appears to create a "managed" connection, but doesn't truly do so. In fact, if you attempt to use code in the comment as is - you're only halfway there. How do you actually use this "ConnectionManager" object anyway? There's no "Open" method, or Command object - just weird methods and properties that would be entirely unfamiliar to you even if you know .Net data access! And to add insult to injury, even if you did know where to go from here, the example provided will only cause an error, and will never work.
What is a Managed Connection?

Lets start with describing what a "managed" connection is (in the context of SQL Server Integration Services). It is simply a connection that's defined and controlled by an entry in the "Connection Managers" window at the bottom of the design surface in BIDS.

Managed connections have the following benefits:

Their properties (primarily the connection string) can be manipulated with Package Configurations.
Their properties (again, the connection string) can be manipulated with Property Expressions.
They can participate in SSIS (MSDTC) transactions.
The connection is tested by default when the package is loaded, before any (potentially unrecoverable) work is started.

But perhaps most importantly, using a managed connection within a script (properly) informs someone maintaining the package that a connection to an external resource is being used.
How is the Advice Wrong?
The comment in the Script Task counsels you to use Dts.Connections.Add to get a reference to a ConnectionManager object. This is the correct type of object - it's a managed connection. Unfortunately, using that syntax, you're creating a new one, not using one of the managed connections you've already defined in the package. Since it's an entirely new connection, you have to set up the connection string. You could pass the connection string in to the script using an SSIS variable, or read it from a file - but these are both non-standard practices. Another ETL developer or administrator familiar with SSIS won't anticipate your decision to implement a connection that way.
Additionally, since it's a brand new object, none of the regular design time facilities are available to you; Package Configurations, Property Expressions, the TransactionOption property, and runtime validation don't work.
The Right Way to Use Managed Connections
Ignore the comments in the Script Task - use managed connections properly by following these steps.
1. Create an ADO.Net Connection
Add a Connection Manager to your package the same way you'd make one for use in a Data Flow or Execute SQL Task. Right-click in the Connection Managers pane and add a new ADO.Net connection. You can use OLEDB type connections and other connection types - but they're more difficult to use and have fewer capabilities inside a Script.
2. Retrieve a Reference to the Connection
Inside your Script Task, instead of using Dts.Connections.Add, retrieve a reference to an existing connection manager by using the Dts.Connections indexer. For example, if you happen to have a connection manager named "(local).master", then use this code to retrieve a reference to the ConnectionManager object:
ConnectionManager cm = Dts.Connections["(local).master"];
3. For Connections with Providers, Acquire a Connection
(A "provider" means any connection that has code supporting and managing the connection - like an ADO, OLEDB, or Excel connection, but unlike a Flat File connection.)
Even though you now have a reference to the ConnectionManager object, you need to acquire an actual connection to the resource, and request that connection be established in a way that SSIS can manage it. This step is necessary to ensure that your work with the connection participates in transactions and connection pooling (or lack of it).
The specific details for each connection type vary, but the general method is consistent.
// For an ADO.Net ConnectionManager using a SqlClient provider
System.Data.SqlClient.SqlConnection sqlConn = (System.Data.SqlClient.SqlConnection)cm.AcquireConnection(Dts.Transaction);
// For an ADO.Net ConnectionManager using an ODBC provider
System.Data.Odbc.OdbcConnection odbcConn = (System.Data.Odbc.OdbcConnection)cm.AcquireConnection(Dts.Transaction);
Be aware that it is NOT necessary (or advisable) to use the Open method on the connection object you just retrieved... it's already open. (Check the connection's State property if you don't believe me.)
4. Use the Connection
At this point, you can use the connection to retrieve datasets, execute commands, or do just about anything else you'd want to do with your connection.
5. Release the Connection Properly
Once you've finished your work, you need to clean up after yourself - but NOT by using the Close method on the connection! You'll need to use the ReleaseConnection method of the ConnectionManager instead.
How Hard Was That?
Hopefully not so hard as to make you want to go the other direction and use completely unmanaged connections in your scripts! To recap, here's a complete code listing of how to correctly use an ADO.Net managed connection for a SqlClient provider within a Script Task:
bool fireAgain = true;
ConnectionManager cm;
System.Data.SqlClient.SqlConnection sqlConn;
System.Data.SqlClient.SqlCommand sqlComm;
int rowsAffected;
// Retrieve the reference to the managed connection
cm = Dts.Connections["(local).master"];
// Request an open connection
sqlConn = (System.Data.SqlClient.SqlConnection)cm.AcquireConnection(Dts.Transaction);
Dts.Events.FireInformation(0, "", "Connection is: " + sqlConn.State.ToString(), "", 0, ref fireAgain);
// Do your work
sqlComm = new System.Data.SqlClient.SqlCommand("UPDATE YourTable SET YourColumn = 'SomeValue'", sqlConn);
rowsAffected = sqlComm.ExecuteNonQuery();
// Inform SSIS you're done your work
cm.ReleaseConnection(sqlConn);

Dts.Events.FireInformation(0, "", rowsAffected.ToString() + " rows updated.", "", 0, ref fireAgain);

Dts.TaskResult = (int)ScriptResults.Success;