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'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.
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
