I'm having a problem when adding a new table to an existing datamart. I have a field called ID which is an identity and then a number of other fields (which bring across soft credits). I cannot work out how to tell TIE to not try to import into the identity field so I am getting this error. [red]Stopped with error: Cannot insert explicit value for identity column in table 'WH_GiftSoftCredit' when IDENTITY_INSERT is set to OFF.[/red] Does anyone know how to enable identity insert? My xml is below: [code] ... <TABLE name="GiftSoftCredit" primaryKey="GiftSoftCredit.ID.field" id="GiftSoftCredit.table"> <FIELDSET> <FIELD name="ID" dataType="Int" size="4" id="GiftSoftCredit.ID.field" isIdentity="true" /> <FIELD name="RESystemID" dataType="Int" size="4" id="GiftSoftCredit.RESystemID.field" noIndex="true" /> <FIELD name="GiftID" dataType="Int" size="4" id="GiftSoftCredit.GiftID.field" /> <FIELD name="ConstituentID" dataType="Int" size="4" id="GiftSoftCredit.ConstituentID.field" /> <FIELD name="Amount" dataType="Money" size="8" id="GiftSoftCredit.Amount.field" noIndex="true" /> <FIELD name="Percentage" dataType="Int" size="4" id="GiftSoftCredit.Percentage.field" noIndex="true" /> </FIELDSET> <SOURCESET> <DATAPROVIDER SharedProviderID="OLEDB_SHARED_Conn1" connectionType="OLEDBProviderCmd" connectionString="" sourceGUID="0f8ede6f-9845-4334-8f95-46ee8d0d66e3"> <COMMANDTEXT> select GiftSoftCreditId RESystemID, g.id GiftID, c.ID ConstituentID, sc.Amount, sc.Amount*100/g.Amount Percentage from HTAUAT01.RE_TEST.dbo.GiftSoftCredit sc inner join WH_Gift g on sc.GiftId = g.RESystemID --Join to TIE's gift table to make TIE's gift ID available inner join WH_Gift c on sc.ConstitId = c.RESystemID --Join to TIE's constituent table to make TIE's constituent ID available </COMMANDTEXT> </DATAPROVIDER> </SOURCESET> </TABLE> ... [/code] I have got around this by specifying which field to insert into but I would prefer not to use this method. [code] ... <TABLE name="GiftSoftCredit" primaryKey="GiftSoftCredit.ID.field" id="GiftSoftCredit.table"> <FIELDSET> <FIELD name="ID" dataType="Int" size="4" id="GiftSoftCredit.ID.field" isIdentity="true" /> <FIELD name="RESystemID" dataType="Int" size="4" id="GiftSoftCredit.RESystemID.field" noIndex="true" /> <FIELD name="GiftID" dataType="Int" size="4" id="GiftSoftCredit.GiftID.field" /> <FIELD name="ConstituentID" dataType="Int" size="4" id="GiftSoftCredit.ConstituentID.field" /> <FIELD name="Amount" dataType="Money" size="8" id="GiftSoftCredit.Amount.field" noIndex="true" /> <FIELD name="Percentage" dataType="Int" size="4" id="GiftSoftCredit.Percentage.field" noIndex="true" /> </FIELDSET> <SOURCESET> <DATAPROVIDER SharedProviderID="OLEDB_SHARED_Conn1" connectionType="OLEDBProviderCmd" connectionString="" sourceGUID="0f8ede6f-9845-4334-8f95-46ee8d0d66e3"> <COMMANDTEXT> [red]insert into WH_GiftSoftCredit ( RESystemID, GiftID, ConstituentID, Amount, Percentage )[/red] select GiftSoftCreditId RESystemID, g.id GiftID, c.ID ConstituentID, sc.Amount, sc.Amount*100/g.Amount Percentage from HTAUAT01.RE_TEST.dbo.GiftSoftCredit sc inner join WH_Gift g on sc.GiftId = g.RESystemID --Join to TIE's gift table to make TIE's gift ID available inner join WH_Gift c on sc.ConstitId = c.RESystemID --Join to TIE's constituent table to make TIE's constituent ID available </COMMANDTEXT> </DATAPROVIDER> </SOURCESET> </TABLE> ... [/code] -- Stephen Newberry e: [Email Removed]

It sounds like you are confusing IDENTITY and PRIMARY KEY. Both are unique and neither one can contain NULL values, but there are several differences between the two. The most relevant difference being that IDENTITY is typically auto-generated by SQL Server and thus it complains when you try to insert a value from some other source. I see that you already have the GiftSoftCredit.ID.field set to be the primary key. I would simply change the isIdentity property of that field to false and it should fix your problem. Drew J. Drew Allen Children's Hospital of Philadelphia [Email Removed]

Hi Drew I do want the first field to be auto-generated by the server and I only want to populate the other fields. Do you have any idea what I need to do to be able to just insert into the other fields? Thanks, Steve -- Stephen Newberry e: [Email Removed]

I haven't tried this, but instead of making it the first field, try making it the last field. TIE maps the fields from the beginning of the SOURCESET and the beginning of the FIELDSET. Given this constraint, the easiest way that I can see to not supply an explicit value to the IDENTITY column is to have the IDENTITY be the last column in the FIELDSET and to have the SOURCESET have one fewer columns than the FIELDSET. It doesn't matter to SQL where in the table the IDENTITY column appears. Drew J. Drew Allen Children's Hospital of Philadelphia [Email Removed]

Thanks for that. Sounds like a good idea. I'll give it a go and let you know what happens. -- Stephen Newberry e: [Email Removed]

Thanks Drew. It worked! -- Stephen Newberry e: [Email Removed]