September 2, 2004 at 4:38 pm
I am having trouble with a transformation that loads data into a table that has an Identity field in it. I have seen this issue addressed, but not for my particular situation.
I am looping through a bunch of files and then dynamically setting the source and destination of my transformation. It works perfectly except for these errors regarding inserting a null in an identity.
The solutions I have read about include: 1) Try checking/unchecking the Insert Identity parameter on the transformation. Doesn't help. Perhaps because the actual transformation is done via ActiveX. 2) Remove the offending column form the Destination of the transformation. I would do this if I could, but because the Source and Destinations are being dynamically set, I don't think I have that option.
I would greatly appreciate any advice on how to get past this. The only thing I can think of is to insert all the data into a temporary table before loading into the final destination via an SP. But this introduces alot more complexity than I would like.
Thanks!
September 3, 2004 at 12:42 am
September 3, 2004 at 5:45 am
Hi,
The answer to your question is pretty straightforward for those of us who have dealt with the same type of problem
Identity columns cannot be NULL, as the value is either generated by SQL Server, or supplied by the user, and must be numeric. If you have NULL data to be inserted to this column, you should use two DTS passes - one with IDENTITY INSERT turned on for those rows that contain values (which should not be duplicates), and one with IDENTITY INSERT turned off, for those rows that contain NULL.
In fact, for the second pass, do not even include the column, ensure that the seed is higher than the highest value in the column (from the first pass), and let SQL Server assign a value.
The most common use of this column is a primary key of sorts for the table, a system-assigned number such as customer or order number. Note that SQL Server does not enforce uniqueness unless a unique index is specified - but the use of duplicate numbers or NULL defeats the purpose of the column. (The only sure-fire way of obtaining a truly "unique" value is to use the "uniqueidentifier" datatype and the GUID property on the column set to true -- not a good value for a "customer id" or "order number" though.
Hope this helps some... We went through that pain - we had "order" rows which may not have contained an order number (order was "in process" or "abandoned"). We changed our application to assign an order number, regardless of whether it was complete or not. Since the column is an integer by default, many digits are available and the chances of "running out" of numbers is very rare.
Good Luck!
-- Joe
September 3, 2004 at 8:48 am
Thanks for the reply.
None of the rows that I am inserting contain values that I want to use as the identity. I would like them all to be created by the database.
The problem is, there doesn't seem to be any way to get the transformation to ignore this Identity field. Since I am setting the destination dynamically and using ActiveX to perform the actual copy, there doesn't seem to be any room for me to remove the identity field from the destination. As I mentioned previously, I have tried turning Identity Insert on and off, no difference.
For your reference, below is the code that does the actual transformation. It basically loops through all the columns and loads into the appropriate destination. The previous step is a Dynamic Properties Task that sets the Source and Destination as there are about 20 such transformations being done.
for varSourceColumn = 1 to maxColumn
varDestColumn=varSourceColumn+varColumnDiff
if (varSourceColumn<maxColumn) then
DTSDestination(varDestColumn)=DTSSource(varSourceColumn)
end if
Next
Note: varColumnDiff is the offset from the Source to the destination. I have ruled out the any logic problems in the code above becasue I tried doing a transformation Explicity listing the transformation of one data column, and the same error occured even though I was not referring to the Identity field in any way.
Any other ideas?
September 4, 2004 at 11:28 am
Maybe create a view on the target table that does not include the Identity column -- and insert into the view?
Bob Monahon
September 6, 2004 at 6:14 am
I think the answer to your problem lies in the 'Enable Identity Insert' setting for the Transformation Data Task.
Right click the Transform Data Task and select Properties. Select the Options tab, then at the bottom of the form - under SQL Server - check the 'Enable Identity Insert'.
This should then make SQL Server insert your Identity column everytime the task creates a new row in the table.
Hope this helps
Stuart Graham
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply