August 11, 2004 at 10:06 am
Greetings SQL Gurus:
I have read several threads on this board and others about related problems but mine doesn't seem to fit -- or else I have overlooked something. Here's the deal:
Running SQL Server 2000 and using the Enterprise Manager client on a Win XP Workstation. I have created a DTS package that inserts data from an Access database into SQL Server. Since there are several transformations that need to happen, I wrote a VBScript transformation function. I am ignoring the identity column (called 'MemberID_') in the destination table, and on the Options tab of Transform Data Task Properties, I unchecked Enable Identity Insert. My understanding is that, since I made no reference to the 'MemberID_' field in my code and turned off identity insert, SQL should automatically create and insert the identity field value, but instead the package fails on execute and I get this error:
"Insert Error, column 18 ('MemberID_',DBTYPE_I4), status 10: Integrity violation; attempt to insert NULL data or data which violates constraints. Unspecified error."
What am I missing? Thanks!
August 11, 2004 at 10:33 am
Never mind; I finally figured it out. I had totally missed the Transformation Options dialog where you must delete the unused field from the Destination Columns selected list. There are way too many dialogs and property sheets in a DTS package!
September 1, 2004 at 2:19 pm
I have a similar problem to whats described above - with an additional wrinkle. I am dynamically setting the Destination table using the dynamic properties task. So I don't really have the option of removing the identity column. (at least that I know of)
To summarize, I am using the same package to load numerous tables. Each table has an identity field in it (the field has the same name - 'batch_id' in all tables). I dynamically set the destination table based on values passed from another package.
Is there any way I can get past this error? Essentially I just want it to ignore the field so the db can populate it.
Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply