There may be occasions where a Prepare instance table has a unique identifier field that has one or more rows with a null value. This article explains two approaches that can be used to create a unique identifier value that can be used in lieu of the null value.
SQL Server ID Functions
The SQL Server functions that can assign a new value to a unique identifier field are NEWID() and NEWSEQUENTIALID().
In SQL Server, however, User-Defined Functions (UDF) are not allowed to use the NEWID() or NEWSEQUENTIALID() functions as both of these are side-effecting functions. Any attempt to use one of these functions in a User-Defined Function will result in an exception being thrown similar to the following:
Error: "Invalid use of a side-effecting operator 'newsequentialid' within a function"
The reason for the exception being thrown is because a function cannot be used to insert data into a base table. Instead, functions are used to return data, such as the GETDATE() function.
Transformations in TimeXtender Data Integration are wrapped in User-Defined Functions, and therefore are not allowed to call either the NEWID() and NEWSEQUENTIALID() functions, which could be used to assign a new unique identifier value during a transformation.
The following two options may be used to design around this limitation.
Note: the NEWID() and the NEWSEQUENTIALID() functions will create a new GUID each time the table or view is executed.
Create ID using Custom Field
- Add a new custom field to the table of type uniqueidentifier.
- Add a “Default table column value” transformation, using either the NEWID() or NEWSEQUENTIALID() function.
- Add a transformation and condition to the destination field, assigning it the value of this new field when the destination field is null.
Please be aware that default values are not assigned to custom rows (custom data), due to the use of an SQL bulk copy operation. During a bulk copy nulls and defaults are not assigned in order to preserve nulls from the source.
Create ID using Custom View
- Inject a custom view in between the source and destination tables.
- Add a CASE construct to assign either the NEWID() or NEWSEQUENTIALID() function to the field when it is null.
The following is an example of using the CASE construct for this type of view.
SELECT
[DW_Id],
CASE
WHEN [My GUID] IS NULL THEN NEWID()
ELSE [My GUID] END AS [My GUID]
FROM [Some Owner].[Some Table]
