views:

907

answers:

4

Am I the only person on this particular planet that is inconvenienced by the lack of Identity Insert, missing in all relevant SSIS Data Flow Transformation objects?

Only the Bulk Insert Task supports Identity Insert, what about elsewhere in SSIS?

Is there a technical change in SQL Server 2008 that makes this very, very limited support for Identity Insert in SSIS necessary?

Because of this "little" detail, I prefer to use C# data procedures that call SQL Code wrapped in SET IDENTITY_INSERT [MyTable] ON and SET IDENTITY_INSERT [MyTable] OFF.

+1  A: 

Yes, this is a bit annoying.

You can put an Execute SQL Task before/after your dataflow which sets IDENTITY_INSERT ON/OFF and wrap it all in a transaction.

Tom Ferguson
A: 

For what other task do you want IDENTITY INSERT? You don't seem to be talking about Data Flow tasks.

John Saunders
+1  A: 

See the link http://www.dosql.com/blog/?p=350 and you'll find you can set an option Keep Identity in the Destination Editor.

Sanghoon
A: 

Use OLEDB source and destination objects. That gives you identity insert option support. I agree this is a bad deal for ADO.net objects.