Unique Identifier Flat File Import with SSIS
This morning I found myself wrestling with SSIS to convert a
string from a flat file to a unique identifier via a Data Flow inside of an
SSIS package. I figured this would be a rather trivial case (the midget was kicking my ass!). I use
guids all over for application development and for database keys. They
are very useful, and very dependable. Here is a
line from my flat file:
61898d4c-3a1b-4736-9a36-eb90a23322e0|:|10/13/2008 11:59:58
PM|:|74afebfe-9523-4977-b5f6-5154b6c91211
Anyone see anything wrong with this? I didn't see anything
wrong, outside of the silly |:| delimiter (I don't want to get started on that
<rant/> <--- rant with no content!).
So moving forward, I created a Flat File Source, pointed to the
file, had it guess the appropriate type for import (it selected a 36 character
dt_str - fine with me), and then went into the advanced edit section and
changed the output for the column to be a unique identifier. I would expect the conversation to take place
in the middle somewhere, and if the powers that be smile kindly upon me, all
should work out well. Unfortunately, I
would get an error about truncating data, "The value could not be
converted because of a potential loss of data." I was very confused by this. I tried to push the string input from the
flat file data through a Data Converter step and do the conversion there, but
that had the same results. Basically
from that point on for about an hour, I tried every different way I could think
of to convert that guid string into a SQL unique identifier.
Eventually I gave up. The
web turned up little help, and I was nearing my wits end. I don't enjoy working on SSIS packages, and
this was not helping. Thankfully, I have
an in-house SQL ROCKSTAR!! My good
friend Chuck (a
SQL MVP) has helped me though all sorts of SQL issues in the past. I asked him about this issue, and although he
wasn't completely sure, he thought the error might be because I didn't wrap the
guid strings in braces. <rant> I
thought he was absolutely insane! Why
the heck would SSIS expect those guids to be in braces!? Why couldn't it cast them as unique
identifiers? I can say ‘61898d4c-3a1b-4736-9a36-eb90a23322e0'
as uniqueidentifier, and the T-SQL world doesn't come to a grinding halt!!
</rant>
The new input looks like this:
{61898d4c-3a1b-4736-9a36-eb90a23322e0}|:|10/13/2008 11:59:58
PM|:|{74afebfe-9523-4977-b5f6-5154b6c91211}
I went through the steps above, and tested the solution. That's it! It works like a
champ now. Thx u Chuck!!
Twitter: @DavidJustice