December 30, 2005 at 1:25 pm
I have a set text file's that I bulk load into temp table's via a bulk load task.
A Transform Data Task then perform's some joins on these temp table's and insert the data refined data into another set of tables.
I want these final set of tables to have an identity column as the primary key, but since no identity column exists in the source data (or temp tables) my load task always fails.
Is there a way to have the DTS package insert a value for the identity column? Checking off enable identity inserts does not work.
I can acheive this by creating a table with just an identity column. I then have two lookup's one that insert's a dummy value, and another that selected the max value. I can use these lookup's in an Active X script and acheive what I want. This seem seem's like a hack to me, not to mention it slows down the load considerably. Is there a better way?
Thanks,
Ken
January 2, 2006 at 4:09 am
I don't know much about DTS, but generally if IDENTITY_INSERT is OFF, you should be able to insert data into a table with identity column and the values for identity column will be created automatically. Just make sure that you don't name the identity column in the list of columns at all - SQL Server handles this.
Example:
CREATE TABLE test (id_test INT IDENTITY, col1 varchar(5), col2 varchar(5))
INSERT INTO test (col1, col2) VALUES ('A','fgh')
You see that column id_test is not mentioned in the INSERT INTO - that's because you are not inserting anything, you let SQL Server to generate the number automatically. Only with identity insert ON you have to name the identity column and assign a value for it.
Hope this helps, I think it should work the same in DTS... if I'm wrong, then it is up to some more experienced people to solve your problem.
Vladan
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply