Insert to table with SQL Server uniqueidentifier constraint

dataservices job design
1

Hi All. I’m trying to load records into a SQL Server 2012 table that has its primary key column defined as a ‘uniqueidentifier, not null’ data type. It also has a constraint such that a new record must have the next sequential uniqueidentifier as its key value (the constraint uses the SQL Server newsequentialid() function).

As Data Services doesn’t recognise the ‘uniqueidentifier’ datatype, I’m failing to load the table. Has anyone elsewhere managed to implement this scenario?

Nemesis :australia: (BOB member since 2004-06-09)

2

If you remove the PK from the field in the data store then do not include the field in your query (let SQL server handle it) then it should load and increment by one with each value you have from source.

Only tested this with a couple of records but seemed to work ok on 4.2 with SQL server 2012.

juno (BOB member since 2008-06-23)

3

Thanks for replying.

How would this work? The key column isn’t an identity column so it won’t automatically create an entry.

Nemesis :australia: (BOB member since 2004-06-09)

4

Sorry misread the initial post, I created a table with the attached script. When I import the table into my DS it recognises It as Varchar I then followed similar steps above and the table loads the values I present and generates the unique ID’s.

Does your data store not even recognise the column?
mytable.txt (0.0 KB)

juno (BOB member since 2008-06-23)

5

Well, I finally got round the problem by using the following code in the output column:-
substr( sql( ‘EDW_STAGE’,‘select newid()’),0,36)

But I’d still be interested in seeing how you got your dataflow to work. Any chance of attaching an atl file?

Nemesis :australia: (BOB member since 2004-06-09)