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.