The SQL Server functions to assign a new value to a uniqueidentifier field are NEWID and NEWSEQUENTIALID.
In a SQL Server User-Defined Function (UDF) you are not allowed to use side-affecting functions/operators. NEWID and NEWSEQUENTIALID are both causing side-effecting, whereas i.e. GETDATE is not. When using i.e. NEWID in a UDF, SQL Server throws an exception like "Invalid use of a side-effecting operator 'newsequentialid' within a function."
Transformations in tX is wrapped in UDF's, thus according to the above it does not allow the use of any of the two functions to assign a new uniqueidentifier value during transformation. There are at least a couple of ways to design around this.
- Add a new custom field to the table of type uniqueidentifier
- Add a default transformation, using NEWID/NEWSEQUENTIALID
- Add a transformation and condition to the destination field, assigning it the value of this new field when the other is empty
Please be aware that default values are not assigned to custom rows (custom data), due to the use of a SQL bulk copy operation. During a bulk copy nulls and defaults are not assigned, to allow keeping nulls from the source.
- Inject a custom view in between the source and destination table
- Add a CASE construct to assign a NEWID/NEWSEQUENTIALID to the field when empty
The following is an example of using a CASE for this purpose
WHEN [My GUID] IS NULL THEN NEWID()
ELSE [My GUID] END AS [My GUID]
FROM [Some Owner].[Some Table]