Finally, a Universal Data Integration Utility

Earlier today, the fine folks at the F. Oobar Corporation released a revolutionary product: a universal data integration utility. This software component, known as the Baseline Ongoing Generic Utility for Synergy, will run on any platform and can convert data to and from almost any format automatically. It also reads the semantics of the data to determine exactly how it will be used and will model the data structure accordingly.

Oobar’s director of engineering, April Fuhl, made the announcement on the Oobar website earlier today. “This is a product that we’ve been working on since exactly this time last year, and we’re very proud to have produced it. Baseline Ongoing Generic Utility for Synergy renders all other data integration tools obsolete.”

According to the documentation on the company’s website, the universal data integration utility will automatically detect and process data in almost any format, including

  • Plain text
  • PDF
  • Binary
  • Haiku
  • Wingdings
  • Whiteboard text
  • Spiral notepad
  • Napkin scribble
  • Fleeting thoughts

Oddly enough, the only known format it can’t process is Excel.

There is an optional service that will fix data quality issues automatically. Known as UWIMNWIT (Use What I Meant, Not What I Typed), it will infer from the operator’s data entry speed, body temperature, and facial expressions what the data should really show. It is also smart enough to detect keystroke pressure, and will automatically uppercase any data that was typed in angrily.

This utility is not only versatile but efficient; because it uses the smallest font size possible during the processing of the data, it can move large amounts of data very quickly. Data sources and destinations that use Comic Sans have seen 2x throughput when compared to other font faces.

The Baseline Ongoing Generic Utility for Synergy is lightweight, and will run on any system with a 5.25” floppy drive.

If this amazing product sounds to good to be true, I encourage you to check out the official announcement page. Be sure to place your order before the end of this first day of April 2015 for a special discount.

Why I Want to Have Coffee With You

10145153253_b3bf50d2d6_z Yesterday I read an article entitled “Why I Don’t Want to Have Coffee With You”, in which the author writes that he doesn’t have the time or the desire to simply “have coffee”. While I empathize with some of the author’s justifications for his position, I was disappointed at the hard line he took on this. Personally, I prefer a completely different approach to professional requests for coffee, lunch, etc.

When I started in this business some 16 years ago, my market value was limited. As a young buck with no college degree, almost no experience, and few contacts, I wasn’t the type of person that most people would go out of their way to hire. I had a lot of enthusiasm and aptitude, but with little in the way of actual experience, my options were limited. I had to take whatever I could get to start building my skills and my résumé.

Although it was well outside my comfort zone to do so, I reached out to others in the industry who appeared to be successful. To my surprise, some of them actually talked to me. Not all of them did, of course, but I was pleasantly surprised at how many successful technical professionals were willing to sit down and visit with me to hear about my ambitions and let me ask them questions about their success. A few of them gave me really good advice about how to actively manage my career. Later, some of these folks ended up being colleagues or clients, and in a few cases, friends.

As I built up my experience, I continued this tradition, actively engaging some of the folks whom I admired in technology and business. But a funny thing happened along the way – others started reaching out to me for advice and counsel. I’m pretty sure I laughed out loud the first time someone asked me for career advice, because it sounded silly at the time. However, I’ve found that if you know just one thing, there’s always someone else who doesn’t know that one thing and might benefit from your experience. So I happily accepted requests to help out others in the same way I was helped during my green years.

Later, as I matured in my career, I saw this come full circle. In agreeing to these casual requests to meet, I had – somewhat accidentally – built a network of others in the technology business, and a few of those relationships paid off as casual contacts were converted into employees and clients. However, even in cases where my coffee companion didn’t turn into a formal business associate, I (and they as well, I hope) benefited from having shared time discussing our experiences and perspectives. If I’ve learned anything from all this, it’s that a fruitful business relationship doesn’t always require an inked contract.

So, having revealed some of my history and bias in this matter, I’ll tell you why I do want to have coffee with you.

1. People are my business.

By trade, I am a technical consultant. However, if my focus were just on the technology, I would be out of business. The truth is that I’m not a tech guy – I’m a business professional who knows how to use technology to solve problems. To solve those types of problems, I need to understand those pain points, which usually cannot be fully diagnosed with a database script or an automated process. These problems have to be articulated, and more often than not, I must put forth a lot of effort and analysis to ask the right questions so I can get to the root of the problems. My business is understanding people, and the fact is that having coffee with you will improve my interpersonal and communication skills. When I ask about where you are in your career, where you’d like to go, and what you think you should be doing, I’m honing my craft – remember, I’m in the people business – which will help me on my next client, and the one after that.

2. It’s a small world.

More precisely, it’s very big world, but the circles in which we travel tend to overlap a lot. The person I have lunch with today might be the one who knows someone who will ask tomorrow for a recommendation for a business intelligence consultant. On the flip side, the person whose coffee invitation I reject might soon start a new job with a Fortune 100 company in need of exactly the services I offer. Both of these people will remember me, and my acceptance or rejection will help to shape their perception of me. When I accept your invitation to coffee or lunch, since I’m a people person (remember the prior bullet?), I’m optimistic that I’ll make a good enough impression that you’ll remember me positively and call on me, or perhaps refer me to someone else in your circle.

3. I’m returning a favor.

Yes, I’m returning a favor, but chances are good that you weren’t the one who extended me the favor I’m repaying. As I mentioned, there were numerous others who helped to guide me when they had no obligation or prior history that required them to do so. Whether you call it karma, the golden rule, or simply paying it forward, I’m trying to help people in the same way that others helped me. In some cases, I’m going out on a limb for someone who will never directly become a business associate, but that is not the only metric I used to measure the success of these types of meetings.

4. You offer a fresh perspective.

Too often, businesses – and technical businesses in particular – spend a lot of time in silos. In the echo chamber inside of an organization, ideas can sound incredible when in reality the product or service being built could be something that nobody really wants. When we meet for coffee, I’m going answer your questions and offer whatever counsel I can, but I’m probably going to casually bounce an idea or two off of you as well. Further, getting an outside opinion helps me to better understand industry trends. Do you think this cloud thing is going to stick around? What are your thoughts about the next version of Windows? Are you having a hard time finding good people to hire? I’m not just making chitchat when I ask these questions – your perspective will help me understand the technical and business ecosystem in which we live.

5. You might someday become an employee, client, or business partner.

Notice that I didn’t say that there’s a good chance we’re going to ink some kind of deal. We might do business together. If I’ve learned anything as an independent consultant, it’s that you can’t always predict where business relationships will come from. I’ve seen deals that were practically guaranteed end up falling apart without explanation. I’ve also seen business materialize out of seemingly insignificant encounters. I’m not building a business to be successful just in the short term, and while my spending a half hour at Starbucks with you might not pay off today or even this year, chances are good that one of those lunch/coffee dates I accept today will pay off down the road.

Now, I am a realist. I recognize that because of time constraints and logistics, I won’t be able to fulfill every request I get to meet up. I agree with the author of the article above that client work does come first, and I concur that one shouldn’t neglect family responsibilities to abide every request to network. However, since I don’t have a crystal ball to know who will and who won’t contribute to my business, I’m not going to arbitrarily refuse a coffee invitation simply because I don’t see an immediate return on my time.

The Idea Book

How many times have you said to yourself, “Someone should build an application that does [x]…”, or “Wouldn’t it be easy to add automation to [y]”, or “It would be a lot of fun to work on a project to build [z]”? For me, this has happened a lot, and seems to occur more frequently the longer I’m in this business.

image In the early days of my career, these ideas were all great – for someone else. Someone with more skills and experience than me. Someone with more connections. Someone more confident, good-looking, eloquent. But definitely not me. I subconsciously put onto pedestals those other someones who actually could do the things I thought up. Sure, I had the occasional wild thoughts like “What if I were to build the next eBay?”, but thoughts of realism and self-doubt always reigned me in. I would move on, sooner or later forgetting that I’d even come up with the idea.

However, a few years into my career, I turned a corner. I started to mingle with those who were creating solutions, and I began to realize that they weren’t demigods. I learned that folks who were building successful widgets weren’t vastly smarter or more talented than me. They were just ordinary people who had an idea, explored its potential, and then worked like crazy to make it happen. It was this realization that led me to believe that I, too, could dream up and build successful widgets. From there, my Idea Book was born.

Yes, it really was a book

Version 1 of my idea book really was a physical book. More specifically, a spiral notebook. I carried it with me and would jot down ideas big and small. Most of the ideas I wrote down in my idea book were dumb. Many were wildly ambitious and unattainable by anyone at any skill level. Others were so narrowly focused that their implementation would benefit no one. Still others were solutions looking for problems that didn’t exist. The vast majority eventually found their way to the cutting room floor, and only a fraction of them had enough merit to really pursue. In spite of all of these deficiencies, my idea book has been a resounding success. I’ve carried several of the ideas to implementation. Some of them have made their way into the code tool bag that I use on a nearly-daily basis. A couple of the ideas from my idea book have made me a lot of money.

Having an idea book doesn’t make it a success, obviously. For the first year or two, my book was where ideas went to die (or more accurately, to fall into a coma). I was also timid at first about adding ideas I wasn’t sure would be practical or doable. My idea book didn’t bloom until I committed to two concepts: no idea is too big/small/ridiculous to be added to the idea book, and I must actively push the ideas through the funnel.

All ideas are welcome

Over time, my idea book has transformed from a physical notebook to a virtual one (I use Evernote). I’ve also worked up a semi-structured idea funnel, which I used to categorize ideas into buckets including brainstorming, researching, and in-progress. I’ve also resolved to allow any idea, however grandiose/trivial/odd, into my idea book. My idea book is mine and mine alone, and it doesn’t get shared with anyone – not even my wife or my business partners will ever see the unedited version. Any and all ideas are welcome, though not all will survive the vetting process.

My idea book is with me all the time, in one form or another. If I’m riding in an elevator and a new idea strikes me, I type out a quick note into my phone app to capture the moment. If I’m in the middle of a presentation and someone asks a question I want to pursue later, I scratch out a note on my spiral pad (yes, the old-fashioned method still works sometimes). On occasion, an idea will strike me in the middle of the night, and I’ll grab my Surface from the nightstand and jot down my idea. The medium(s) used to log the ideas isn’t critical, so long as I’m faithful about getting them into Evernote as soon as practical.

The real value shows through when I’ve got 20 minutes to spare, and I’m looking for something to fill the time. Sitting in the lobby of the Kwik Lube, waiting for my oil change. Waiting to see the doctor. On hold with tech support. Using my idea book (the new virtual version), I can review ideas I’ve previously logged, and sketch out pros and cons, flesh out why they will or won’t work, and weigh the benefits against the investment. Having my idea book easily accessible makes it easy to do this in bite-sized chunks rather than having to schedule several hours to go through these ideas.

So where is your book?

Should everyone have an idea book? Absolutely. Whether you’re a technical professional or not, newbie or seasoned veteran, a maker or a strategist, a coder or an executive, having an idea book can stoke the creative fire in you. Thinking through new ideas, analyzing them, and implementing the good ones helps keep you thinking about alternative approaches and different ways of doing things, and can keep you out of a career rut. And who knows, your idea book might end up producing the next eBay, Facebook, or Rubik’s Cube.

Fix Inconsistent Line Terminators in SSIS

There is a flat file processing issue I’ve run into a number of times over the years, and it’s come up again several times recently. The issue relates to the line terminators used in data files. Occasionally, changes to the systems generating these data files, or perhaps even manual edits, can change the way the file marks the end of a line. These changes can cause a failure of package execution, or even worse, they can be loaded successfully and cause data quality issues in the target.

In every text file, there are unprintable characters called line terminators that mark the end of each line. On most UNIX and Linux systems and some older operating systems, files are created using the line feed character (LF, or ASCII character 10), while files generated in Windows typically use the carriage return and line feed (CRLF, or ASCII characters 13 and 10, respectively) to mark line endings. The tricky part is that these characters are generally not displayed, so opening up a data file with Notepad or a similar editor will not present any visual differences between line feed-terminated files and those using carriage return plus line feed. However, these differences can have a significant impact on ETL processes, as well as any downstream systems relying on data from those files.

In this post, I’ll show some examples of both LF and CRLF terminated files, and how they behave in SSIS when the package is expecting one type but gets the other. I’ll then demonstrate two workarounds to prevent unexpected errors due to changing line endings.

The scenario

My client, Contoso, has a new supplier, and I’m setting up the ETL process to import some flat file data from this new supplier. I’m working from a file spec that includes column-level mappings and indicates that lines will be terminated using CRLF. I build the package, test it against sample data provided by Contoso’s new supplier, and send the successfully tested package to QA for final testing and deployment to production.

Weeks go by, and the package works great. However, one morning I get a call from Contoso, asking for advice in troubleshooting this new process. It appears that the package has failed without loading any data, logging a data truncation issue upon failure. Both Contoso and their new supplier have reviewed the data file causing the failure, and cannot find any reason for the error. I open the file up in Notepad++ and turn on the Show Line Endings feature, and the problem becomes readily apparent. The most recently successful file looks like this:

image

However, the failed file looks like this:

image

The difference is subtle but important: The second file uses the line feed character as a terminator, while the previous file uses a carriage return. This distinction is not visible when using tools such as Notepad, and in fact, even in Notepad++, these characters aren’t shown by default. However, even though these characters are not visible by default, the distinction is very important.

Why does it matter?

Although they are easy to forget about, incorrect line endings can wreck an ETL process. As shown in the hypothetical scenario above, in cases where the SSIS package expects to receive CRLF line endings but instead gets just an LF, most likely the package will fail due to either data truncation or data type issues. Even worse, if the package is set up to process LF line endings but receives a file with CRLF terminators, chances are good that the data will actually be loaded – with some extra baggage. In the latter case, if the last data field on the line is interpreted as a character data type (CHAR, NVARCHAR, etc.), the carriage return character would be preserved in the loaded data. In the example below, I’ll show how this can impact the quality of that data.

For this example, I’ve created an SSIS package to process a data file using LF line terminators. Then, I regenerate the same data file using CRLF line endings, and process the modified file. The package successfully loads the file, with no apparent problems. Below I can see in my target table that the data has been loaded.

image

Now, I want to find all products matching the first ItemName in the list. When I query this table using the exact ItemName value I just saw in my SSMS results window, I find that I get no results at all.

image

Even though I’m typing in the exact description I see, I get no results for that value. The reason is that I’m looking for the literal string ‘Mountain-100 Black, 42’, when in reality, the value in this field contains an unseen carriage return. Because the SSIS connection was configured to use LF as the line ending, it interprets the carriage return to be part of the data, and loads it to the output table. Copying that value from the SSMS results grid and pasting it into the query window confirms that the extra CR character is present at the end of the data value. Knowing this, I can modify the section criteria I used, changing the query from an exact match to a LIKE with a wildcard at the end to return the values I expected to see.

image

This confirms that the line ending is the problem, but what can be done to avoid this in the first place?

Fixing the Line Ending Problem

When coding to avoid issues with inconsistent line endings, there are three potential scenarios to plan for:

  • Lines with LF line terminators
  • Lines with CRLF line terminators
  • Lines with CR line terminators (a far less common scenario)

Planning for the first two scenarios listed above is relatively easy; the last one takes a bit of work. I’ll demonstrate the design patterns for handling each of these.

Coding for LF and CRLF

As I mentioned earlier, files originally specified as LF endings then getting switched to CRLF (or vice versa) is more common that you might think. However, this problem is fairly easy to resolve using the SSIS data flow. First, the flat file source should be updated to use only a line feed for the line terminator, as shown below.

image

Next, on the data flow, add a derived column transformation to the data pipeline. This transformation will remove any carriage return values (indicated by “\r” in the SSIS expression) found in the last data field.

image

When using this pattern, the output will be the same regardless of whether the lines in the data file are terminated with LF or CRLF. For the latter, the package will simply remove the extra carriage return in the data flow. This is a very easy pattern to implement, and will provide protection against line endings changing from LF to CRLF, or vice versa.

Coding for CR, LF, or CRLF

Building a package to handle any type of line ending – CR, LF, or CRLF – takes a bit more work. Since the SSIS flat file connection manager must be configured for the type of line ending to expect, preparing for line endings that are not known at design time requires a more versatile source: the script component. Using the System.IO namespace in the script component, I can open the file, read through each line, and parse out the values irrespective of the line endings used.

In this example, I’ve added a new script component to the data flow, and I have configured this as a source. Next, I added output columns to the default output on the script component, which match the metadata in the table to which we will be loading the data. Finally, I wrote the code below which will read each line in the file, assigning the values to their respective columns in the output.

public override void CreateNewOutputRows()
    {
        // Create a connection to the file
        StreamReader reader = new StreamReader(Connections.SalesFile.ConnectionString);

        // Skip header row
        reader.ReadLine();

        while (!reader.EndOfStream)
        {
            string line = reader.ReadLine();
            string[] columns = line.Split(Variables.vDelimiter.ToCharArray());

            // Use an increasing integer for indexing the columns below (so I can be lazy and paste below).
            int i = 0;

            // Add a new row to the output for each line in the file
            Output0Buffer.AddRow();

            // Assign the appropriate values into the output columns
            Output0Buffer.SalesOrderID = int.Parse(columns[i++]);
            Output0Buffer.SalesOrderDetailID = int.Parse(columns[i++]);
            Output0Buffer.CarrierTrackingNumber = columns[i++];
            Output0Buffer.OrderQty = sbyte.Parse(columns[i++]);
            Output0Buffer.ProductID = short.Parse(columns[i++]);
            Output0Buffer.SpecialOfferID = sbyte.Parse(columns[i++]);
            Output0Buffer.UnitPrice = float.Parse(columns[i++]);
            Output0Buffer.UnitPriceDiscount = float.Parse(columns[i++]);
            Output0Buffer.LineTotal = float.Parse(columns[i++]);
            Output0Buffer.rowguid = columns[i++];
            Output0Buffer.ModifiedDate = DateTime.Parse(columns[i++]);
            Output0Buffer.ItemName = columns[i++];
        }
    }

The reason this works in the C# code above and not in the flat file source is that C# treats lines within files a bit differently than the SSIS connection manager does. The flat file connection manager in SSIS has to be configured for a specific type of line ending, while the StreamReader.ReadLine() function simply reads to the end of the line irrespective of the line ending used.

Again, it’s been rare that I’ve had to code for the possibility of three different line ending types. However, I have seen this occur a few times, and for volatile data files, it’s a design pattern worth considering.

Conclusion

In the same way data professionals are usually skeptical of untested data, they must also be mindful of suspect metadata. Changes that appear to be as benign as a modification to the line terminator characters can have a serious negative impact on ETL and its target systems. Using the defensive ETL strategies I’ve shown here, you can help prevent errors or data quality issues related to changing line endings in data files.

Iterating Through Excel Worksheets with Biml

bimlWrangling large or complex Excel workbooks in SSIS can be a challenge. From managing data types (more about that in this post by Koen Verbeeck) to addressing multiple worksheets in a single document, configuring SSIS to properly read from or write to Excel documents is tedious at best. While there are no silver bullets to completely solve these problems, I’ve found that – like many other challenges in the SSIS world – using Biml can help eliminate some of the manual work involved.

In this post, I’ll demonstrate how you can use a few lines of BimlScript code to read through multiple worksheets in an Excel source.

The Challenge

For this scenario, I’m using a single Excel 2007 document containing numerous worksheets. All of the worksheets have a similar structure, each containing results from a different geographic area.

SNAGHTMLb27785d

What I want to accomplish is to programmatically infer from the Excel document the name and structure from each worksheet, and create an SSIS data flow for each one. Doing so can save a great deal of effort, especially if there are many columns, many worksheets, or if the structure of each worksheet can differ from the others.

The Biml Solution

The solution, using BimlScript, is designed as follows:

  • Connect to the source Excel file and loop through the worksheets
  • Create an SSIS data flow for each worksheet
  • In each data flow, create an Excel source component to connect to the worksheet specific to that data flow
  • In each data flow, create an OleDB destination component, and automatically map source columns from the Excel worksheet to the destination table

First, connect to the Biml file as shown below. Note that, if you haven’t already, you will need to install the Excel 2007 driver for this code to work.

  <#@ import namespace=”System.Data” #>
<#@ import namespace=”System.Data.OleDb” #>
<#
var connectionString = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\\AccidentData.xlsx;Extended Properties=\”EXCEL 12.0 XML;HDR=YES;IMEX=1\”;”;
var connection = new OleDbConnection(connectionString);
connection.Open();
var worksheetCollection = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows.OfType<DataRow>().Select(i => i[“TABLE_NAME”].ToString()).Where(i => i.EndsWith(“$”));
#>

This will create an array named worksheetCollection, which contains the names of each worksheet found in the specified Excel file. (Big thanks to Scott Currie of Varigence for sharing the syntax of this operation.)

Next up, I will iterate through this list of worksheets, building an SSIS data flow for each one. I’ll also point out that I could also create one package per workbook, as opposed to a single package with multiple data flows; however, for the sake of simplicity in demonstration, I’m using just one package with a separate data flow per Excel worksheet. As shown below, I’m creating a foreach loop in BimlScript, to loop through each worksheet name. In each iteration of the loop, I’ve created a data flow, with the same name as the worksheet.

<# foreach (var worksheet in worksheetCollection) {#>
<Dataflow Name=”<#= worksheet.Replace(“$”,  “”) #>” DelayValidation=”true”>
</Dataflow>
<# } #>

So far this data flow task does nothing. Let’s add the Excel source connection.

<# foreach (var worksheet in worksheetCollection) {#>
<Dataflow Name=”<#= worksheet.Replace(“$”,  “”) #>” DelayValidation=”true”>
<Transformations>
<ExcelSource ConnectionName=”Accidents Source File” Name=”XLS Accidents – <#= worksheet.Replace(“$”,  “”) #>”>
<DirectInput>SELECT * FROM `<#= worksheet #>`</DirectInput>
</ExcelSource>
</Transformations>
        </Dataflow>
<# } #>

The code above creates a connection to the Excel document, building the SELECT statement using the name of the worksheet as the table name. A couple of things to note here: First, this snippet uses an Excel connection named Accidents Source File, which I set up in a previous step (the code for which is provided in the download). Also, the source name uses the name of the worksheet with the dollar sign ($) removed through the Replace() function in the BimlScript.

Finally, I’ll add the destination connection. Since all of the workbooks in this sample spreadsheet will be loaded to the same table, there is no dynamic code in the destination component. You may notice that this snippet also does not use any column-level mappings. This is by design; since each of the worksheets may have some differences in metadata (an extra column, or missing columns), I’ve excluded any static column mappings, instead relying on automatic column name mapping in Biml. Also, like the source transformation, this destination refers to a source name that was set up previously.

<# foreach (var worksheet in worksheetCollection) {#>
<Dataflow Name=”<#= worksheet.Replace(“$”,  “”) #>” DelayValidation=”true”>
<Transformations>
<ExcelSource ConnectionName=”Accidents Source File” Name=”XLS Accidents – <#= worksheet.Replace(“$”,  “”) #>”>
<DirectInput>SELECT * FROM `<#= worksheet #>`</DirectInput>
</ExcelSource>
<OleDbDestination Name=”OLEDST Accidents Table” ConnectionName=”AccidentData DB”>
<ExternalTableOutput Table=”[dbo].[AccidentData]”></ExternalTableOutput>
</OleDbDestination>
          </Transformations>
</Dataflow>
<# } #>

After saving the Biml file and generating packages, the resulting SSIS package appears as below.

image

Each data flow contains an Excel source connected to the common output table. Each of these Excel sources retrieves data from one worksheet, filtered by the SELECT query.

SNAGHTMLbae36df

The complete Biml file used for this example can be found here.

Conclusion

Working with Excel data in SSIS can be tedious and time-consuming. However, Biml can help by automating some of the manual operations involved in Excel-to-SSIS mappings.

On Failure: Getting Up

WP_20150205_004 In my continuing series entitled “On Failure”, I want to talk about skiing.

I’m not a great skier. It would probably be a stretch to say that I’m a good skier. Still, I enjoy doing it, and I want to (and can) get better at it. Since I live in the Dallas area, I don’t get a lot of opportunities to ski – usually 1-2 trips per year – but I try to make the most of it when I do get to go.

When I first started skiing, I fell – a lot. The first time I went skiing, I took a half-day lesson before I got started, after which I assumed I’d be a decent skier. I was wrong. Beginner ski school is more of a lesson in logistics (how to put on and take off your skis, how to board and deboard the ski lift, etc.) than an exercise in actually learning how to ski. So my first trip down the mountain was rife with tumbles, lost skis, snow burn, and “WHY DO MY LEGS HURT SO MUCH??” On those first few runs, I spent more time in the horizontal than the vertical. Because I had to get out of the snow and put my skis back on every few hundred yards, just completing each run was a slow, painful, exhausting process.

Toward the end of that day, I recall that I had a particularly nasty fall after inadvertently crossing my skis (again). I was exhausted, embarrassed, and hurting. All I wanted to do was to lie there, gather my thoughts, and let some of the pain subside. My friend, an experienced skier, was skiing behind me and stopped to help. When I told him I just wanted to lie there for a minute, he told me, “Get up. Lying in the snow just makes it worse.” Grumbling, I got up and continued the slow trek down the mountain.

I’ve thought about my friend’s advice a lot since then. As a skier, I’ve come to find that his words were quite true. Why?

  • Simply lying in the snow after a spill makes you colder, and the cold coupled with inactivity make it physically more difficult to get up.
  • It’s easier to talk yourself out of continuing when you’re lying there feeling sorry for yourself.
  • You’re physically in danger from other skiers crashing into you from behind.

But this statement doesn’t just apply to skiing. We can all use this advice in our careers and business relationships as well. Many of us have had some nasty spills in our careers, being on the wrong end of business failures, professional quarrels, terminations, and other career maladies. Personally, I can completely empathize with the desire to “just lie in the snow” after a career setback. I’ve been guilty of indulging the instinct to just stop moving forward, attempting to soothe those aches using self-doubt and self-pity, after a career setback. But just like the skiing analogy, such behavior only makes the situation worse. Refusing to move forward after going topsy-turvy will almost certainly impact your relationships and career prospects. Sometimes it hurts to get up and keep moving forward, but simply lying in the snow hurts even more.

Every failure, on the ski slope or in the cubicle farm, requires some small amount of time to regroup. But the key objective is to keep moving forward, even if it hurts to do so at first.

Advanced SSIS Training in Dallas

I’m very excited to offer a new course entitled “Advanced SSIS” in the Dallas area this spring. My friend and colleague Andy Leonard and I will be delivering this new 3-day course March 9-11, 2015 at the Microsoft offices in Irving, Texas. This course is intended for those who have experience using Integration Services who are looking to take their skills to the next level. In this course, we’ll each be sharing experiences and war stories from 10+ years in the data integration space.

Among the topics we’ll cover:

  • Data flow internals
  • Performance design patterns
  • Learning how to fail properly
  • Security in SSIS
  • Deployment design patterns
  • Data quality
  • Metadata management patterns
  • Biml
  • Known limitations of SSIS, and workarounds for them
  • ETL data edge cases

Also, there may or may not be stories of chicken farming.

We’ll bring the coffee and lunch for all three days. All you need to bring is your experience using SSIS and your readiness to advance your data integration skills.  Space is still available, and early bird pricing is in effect until February 6th. If you’ve got a team of folks who would benefit from this training, contact me about a group discount. If you’re coming in from out of town, there are lots of hotels close by, including the NYLO right next door and the Hampton Inn about a mile away.

Feel free to contact me with any questions. We hope to see you there!

On Failure

Well, there's another way not to make a lightbulb.The first rule of blogging is that you should write about topics you know a lot about. And I know a lot about failure. This post will be the first in a series on the topic, through which I’ll share a few of my own failures and how I’ve done my best to use them to my benefit.

In almost every context, the word fail is a negative:

  • Last night’s database backup failed.
  • Our data warehouse project was a failure.
  • We failed to close a deal with this prospect.
  • The boss failed to live up to his promise.

Failure means that something wasn’t done, or was done incorrectly. Failure is a missed deadline. It is a lack of planning, or misplaced trust. Failure is a lost parcel, a lost customer, or a lost cause. It is a business ending, a marriage dissolving, a career plan torn to shreds.  And it’s also an inevitable part of life.

I don’t consider myself an expert on failure, but I’ve experienced enough failures – both large and small – that I can speak with some measure of authority on the topic.  I’ve lived through multiple divorces of parents and grandparents. I’ve lived in poverty on the wrong side of the tracks. I nearly got fired – on multiple occasions – from my first job because of my immaturity and a bad attitude. I dropped out of college in the middle of a semester (and failed to withdraw, of course) and received grades commensurate with dropping out in the middle of a semester. I invested years in preparing for a career I’d dreamed about since junior high school only to discover that I didn’t want to do that anymore. I started a business which failed in under 2 years. I’ve missed out on dozens and dozens of business and career opportunities due to my own procrastination. And those are just the high-level failures I can think of off the top of my head that I’m willing to share – there are many more that I’ve forgotten, and some others are frankly too embarrassing to blog about.

But the beautiful thing is that I’m still here. I’m alive, I’m employed, I’m healthy, and I’m sane (stop laughing – I really am). But even more importantly, I’ve learned that failure is a part of life, and more specifically, it’s a part of my history. For every failure I experienced, for every hardship I brought on myself, I learned something. And because I still fail, I’m still learning.

I don’t know if there’s value to anyone else in my sharing this information. So in that way, this post may be a failure. Except that it won’t.  Even if neither of the people who subscribe to my blog get any value from this, I will have learned something from writing all this down. And at a minimum, I’ll have something that I can refer to on those days after I’ve had a particularly large failure and need a reminder that I haven’t failed in vain.

I realize that some of this may resemble bumper-sticker logic. I promise not to go all-out Tony Robbins on you, but here are a few of the points I’ll cover in this series.

  • Failure is necessary for growth. Not unlike the muscle-building process, to build we must first destroy. Failure is a little bit of destruction, but managed properly, will lead to personal and career growth.
  • Failure of integrity. This is the worst and most destructive kind of failure. How do you get past this?
  • Failure through inaction. Failing to seize an opportunity is a huge source of regret for many (this guy included).
  • Respond properly. You’ve got to know how to respond to failure (yours and that of others) to be able to properly manage it.
  • If you’ve not failed in a big way, you’re not taking enough chances. This is where I’ll tell you all about my business failure and what I learned from it.
  • Failure doesn’t have to be fatal. Failure is not the end of the line. It’s an obstacle in the road.
  • Failure demands both forgiveness and accountability. Learning to forgive failures (especially your own) is critical, but there must be accountability as well.

I’m not necessarily proud of my failures, but I try to remind myself every day to use those failures as a way to do it better next time.

Six practical tips for social media success in 2015

Social media is the new résumé.  In many ways, it’s even better than a résumé – a person’s social media stream can reveal attitudes, biases, and deficiencies that wouldn’t dare appear on a résumé.  Your online thoughts – blogs, Instagram pictures, tweets on Twitter, posts on Facebook, among others – help to make up the digital you, which friends and strangers alike will use to assess who you are and what you can contribute.  The things you share on social media become part of who you are.

Even more importantly, there’s a permanence to social media content that requires us to pay special attention to anything posted on the Internet.  There’s no Undo on the Send button; once you publish something to the Internet, it can be there forever.  Remember that potential clients and employers will most likely review your social media activities before making a hiring decision; in fact, a recent survey of human resources personnel revealed that over 90% of respondents looked to social media when checking out a candidate.  Even if you’re not looking for a job, consider that what you post today may still be around for years afterward.  Sure, you can edit or delete content or restrict its privacy settings, but have you read the terms of service for the medium on which you’re sharing that information?  In some cases, content you share online may be used in ways you don’t expect, according to the provider’s terms of service.  The bottom line is that privacy settings and deletion won’t necessarily keep your content private, so think twice before posting angry rants or NSFW after-hours photos.

With that, here are a few basic rules I try to follow when posting to social media.

Don’t write anything in the heat of the moment, especially if you’re hurt or angry.  Intense emotion often leaves logic behind, and those types of posts tend to be the ones you regret.  If you routinely find yourself posting to social media and later editing or deleting those posts, you might have a problem with this.  Things posted on social media can have a long life span, even when the original media is deleted.  The few minutes of satisfaction you get from sharing that angry tweet, Facebook post, or blog post might cost you years of embarrassment.  Take an hour and walk around the block before you post in an emotional state.

Find your pace.  Everyone has their own speed at which they share on social media.  Some will write a new blog post almost daily, while others do so just once or twice a month.  There are folks who post to Twitter a dozen times each day.  These are all acceptable, but the most important thing to remember is to be consistent.  Don’t publish a dozen blog posts in January and then stop blogging for the year.  Your audience, however larger or small, will follow you in part because of your volume and velocity.  Find a pace that you’re comfortable with, and most importantly, that is sustainable for the year.  The right scheduling tool can help with this, especially when the amount of time you have to devote to social media can vary from week to week.  (As a sidebar, I use HootSuite, though it’s just one of many such tools available, many of which are free.)

Check ur grammar.  I’ll admit it – I’m dogmatic when it comes to proper grammar and spelling, and I evaluate the quality of social media entries based in part on those criteria.  If your posts are littered with misspellings and grammatical errors, you could end up being passed over for a job or a gig.  It’s a fact that some folks are simply more attentive to this than others, so if you struggle with spelling and grammar, find a trusted adviser to proofread your posts (especially longer and more permanent compositions, such as web articles and blog posts).

Rid yourself of negative influence.  The things you read will affect how you write, and negativity breeds negativity.  You know the type – the blogger who complains about everything, the person on Facebook who’s all about drama, or the Twitter follower who’s always posting in anger.  I exercised a social media purge recently, either hiding or completely removing some folks who were constantly angry and negative.  Following people who post a constant stream of bile will almost certainly affect your mood and attitude, and is an unnecessary distraction.  Don’t disengage from someone over one online rant, but if they demonstrate a pattern of this behavior, cut ‘em off.

Have conversations.  Your social media presence can be advertisement, an online résumé, and a series of conversations.  Don’t neglect the last one!  You don’t want to be known as someone who simply broadcasts without listening.  The more you establish yourself as an expert on a topic, the more folks will want to chat with you, whether it’s to ask for advice, share an idea, or simply to get to know you.  While you don’t have to engage with everyone who reaches out to you (see the prior bullet), it’s usually best to err on the side of openness.

Last and most importantly, be you.  Don’t look to mimic someone else’s blog posts, tweets, or Facebook activity.  Your followers will read what you write because it’s yours, not because it resembles that of someone else in the community.  In fact, being different is a good way to gain even more followers; if you’re writing about things few other people are writing about, or if you’re approaching it on a level or from a perspective others aren’t, you’re likely to be different enough from the crowd that people will seek out your content.

Everyone uses social media differently, and each of us will have our own set of internal guidelines on what to post.   Just remember that your social media stream becomes an extension of, and a window into, your personality.  Take care in what you share, pace yourself, and be accessible.

[OT] Blog is in maintenance mode

I’m spending part of this holiday break repaying some technical debt on my website.  Among other things, I am importing some old content that I never brought over when I did my migration to WordPress a few years ago.  Most of the content I’m bringing over is old (most of it is at least 5 years old), and I’m adding it to this site to integrate all of my content, both recent and historical, in one place.  I don’t expect that the old posts will show up in RSS readers as new content.

In addition, I’m planning to try out a new WordPress layout.  I’ve been using the same simple (read: dull) theme for a while now, and I’d like to dress it up just a bit.