MSSQL Table Destination – Identity field
I am creating an integration from GP to a custom SQL table. The custom SQL table has an identity\index field and it is required. Smart Connect wants me to map this field as it shows as required in SC. Is the only way around this is to change my SQL table to not make this field required?
Not sure what you mean by “required in SQL” – perhaps you mean you have it set to “not nullable”.
But I think I see what you mean or at least where you are going.
In this case, you said you are saying you have a primary index on the this table which is likely the Identity column.
The “problem” is that SmartConnect sees the PK and then requires you to map it. To me that makes sense – obviously you then would require those key fields.
The issue that leads to in your case is – since it is required in the UI you have to map it.
I tested this a bit on my system (the GL20000 table in GP is a perfect example of what you are looking at) and I think I see the solution.
The PK on that particular table is the identity column DEX_ROW_ID and it shows as required in SmartConnect.
But then since I don’t want to import to that column (but have to map it), I set my DEX_ROW_ID to a local constant of -1 (because it shouldn’t exist since GP starts at 1)
then I just mapped my data field (journal entry) with a value and marked the ‘allow duplicate’ checkbox.
the SQL that SC generates is:
SET [JRNENTRY] = CONVERT(INT, 888891)
WHERE [DEX_ROW_ID] = ‘-1’
IF @@ROWCOUNT = 0
INSERT INTO [GL20000]
VALUES ( CONVERT(INT, 888891) )
So what I was expecting was an error on the DEX_ROW_ID since I have it mapped – but it appears that SC is smart enough to know that it shouldn’t try to insert to an identity column.
So it does check to see if it exists. Since it does not, then the insert runs which does the proper insert to my table.
Thank you Patrick, that is exactly what I was trying to do. I did the local constant for the PK of -1 but receive an error stating : ‘Invalid object name ‘Bills_AppliedPayments’
I am not sure if you would know the answer to this error. That is the correct table name but it does not use the default schema (dbo), it uses another schema that my developer created. Does Smart Connect require that the table use the dbo schema?
Well, as you can see in my example, SC doesn’t use the schema name when it create the insert/update statements.
So that would mean that _SQL_ would pull the default schema for the user that is making the connection.
So the solution is:
So whomever you have connected to the destination SQL as, you would either:
1. change the schema of the table to match the default schema of that user
2. change the default schema for that user to match the table schema.
Thank you Patrick. I created the custom table in the default\dbo schema and was able to integrate successfully.
Patrick, I am curious as to why you “marked the ‘allow duplicate’ checkbox” in this scenario. Is the idea behind this to allow us to process multiple records with the same “constant” value mapped to the PK of the target table?
Dim rtnvar as integer = Nothing
Then set the Dex_Row_id = calculated function, as it passes Null, the identity column property of the GP table will take care of adding the appropriate next value in the sequence of numbers. We just did this for another client, worked well.
If you would like to submit an answer or comment, please sign in to the eOne portal.