I was reading a discussion about usage of null GUID's and was quite taken back on the logic presented by some. There are instances where a column of GUID type will be null (foreign keys in tables where subordinate table is a transactional based object that can be completed out of process). I have thousands of records where a network goes down and the failover on captures a base part of the transaction. To handle the sync process I load those records into an SSIS object. I have to convert the GUID to string type since the mapping doesn't appear to allow null GUIDs. This code is how I convert that GUID to a string for loading purposes.
CASE WHEN COALESCE(Id, CAST('00000000-0000-0000-0000-000000000000' AS UNIQUEIDENTIFIER)) = '00000000-0000-0000-0000-000000000000' THEN '0'
ELSE CAST(id AS VARCHAR(36)) END Id
I can now map this field into a FOREACH loop and process as normal. Within the loop I have a script that splits the processing between two FOREACH loops; one for processing complete transactions and the other for incomplete transactions.
I hope this helps someone out there.
I'm probably not understanding something, but why can't you just use NULL for the NULL GUID? Or a Blank / Empty String (e.g. in a delimited CSV file or similar)
- GUID's can't be cast as strings. This is enforced by SQL Svr.
- simple '0' can never be a GUID so I can evaluate it as NULL.
- a FOREACH loop object will not take a GUID NULL value. At least I wasn't able to figure out how to make it accept one.
- Adding external files to a project should be used sparingly. Movement from Test -> QA -> Prod becomes more complex is just one reason.
- SSIS strictly enforces data types. When you pull in a GUID from SQL Svr into an Object Variable SSIS determines it's a GUID and works with it accordingly.
Try mapping a GUID type into a FOREACH loop in SSIS from an Object Variable.
I was unsuccessful in performing this using direct mapping of the field.
I don't need the value; just whether or not it's null.
So, I cast at the sql server level and import into the object variable as a string.
Since I can validate a '0' as null using a string I am able to split my transaction processing accordingly.
Also, I'm dynamically looping through 80+ server instances so all of this has to be as dynamic as possible.
Works fine for me:
SELECT CONVERT(varchar(36), NewID()), CAST(NewID() AS varchar(36))
Presumably this is an SSIS issue?
But that means that you have a mix of real GUIDs an invalid (zero) values. Personally I think that is a bad idea - like using "99" as an impossible value, and then all the grief that caused in the run-up to 2000 and the Y2K problem ...
Would
CASE WHEN [Id] IS NULL THEN '0' ELSE '1' END
not solve that? I'm probably being thick, because you presumably considered that
CONVERT(UNIQUEIDENTIFIER, CONVERT(BINARY, 0))
is easier to type than
CAST('00000000-0000-0000-0000-000000000000' AS UNIQUEIDENTIFIER)