DTS Wizard SQL 2005 - Enable Identity Insert

I have a large number of Access tables that I need to periodically bring to SQL using the DTS Wizard in SQL 2000 (via Ent. Man). I am only interested in bring over the data from the MS Access tables as I had a separate script (application drive) that would create the SQL tables.

The problem is this: with SQL 2000 when bringing over the tables, the check box for 'Enable Identity Insert' for each of the tables was checked, this worked fine.

With SQL 2005, this checkbox is not checked hence, I have to click 'edit' for each of the table data being brought over and check the 'Enable Identity Insert' box manualy.

My question is whether there is a way to have SQL 2005 have this 'Enable Identity Insert Box' checked by default as it did with SQL 2000.

Thank you,

Ben

We had to turn off the Enable Identity Insert to make it consistent with the default value for Keep Identity property on OLE DB Source.

Unfortunately, there is no way to change this value for more than one table at the time, in the released Yukon version. We already fixed it for SP1 where you would be able to select multiple tables and do some bulk edits on their transfer settings.

Thanks.

|||Thank you for your prompt reply Bob!

Just to confirm, with SP1 for SQL 2005, we will be able to do bulk edits and change the transfer settings, include the 'Enable Identity Insert'?

Thanks again.|||Yes, you will be able to set Enable Identity Insert for more than one table at once, and plus bulk edit a few other parameters (Drop and Recreate Table, Truncate Table and destination schemas).|||Thank you very much for your response Bob!

Any ideas as to when the service pack will be available?

Thanks!

|||I am not sure if an official date is published yet, but it should be in the first half of the next year.|||Appreciate it Bob!

Cheers.|||

I am having a very strange problem using the DTS Wizard. When I set Enable Identity Insert, the actual ID's on the source server are being changed by the target table. It is my understanding that when we set identity insert to on that the identity column on the target database will accept whatever ID is being passed to it.

It is not working that way with SP1 applied. But it used to work. Am I doing something wrong?

Any help would be apprieciated.

Thanks.

|||I'm having the same problem exporting from SQL 2005 to SQL 2000. It doesn't matter whether "Optimize for Many Tables" is checked or not, even if I am only copying 1 table- the identity values are not preserved.
|||Just checking whether you have updated to the latest 2005 SP1. This should let you select batch tables and change the Identity insert properties for the DTS.
|||Yes, I am using SQL Studio with SP1.|||i have also the same problem, is any one know how to resolve that issue ?|||

It doesn't seem to work. It isn't changing my source tables, but it definately won't insert the appropriate value into the target table. I'm using sp 1 as well.

Does anyone from Microsoft plan to address this?

|||Enable Identity Insert Still does not work when you set it for multiple tables! When will this be fixed?