IDENTITY_INSERT with SSIS OLE DB Destination

Update: Feel free to read my original post (rant included) and the comments thread that follows, but helpful commenters to the original post pointed me to the solution:

If you want to retain your original surrogate primary key values from your source data when inserting into a target table that has an Identity-based primary key, use the OLE DB Destination and choose the Table or view - fast load option for the "Data access mode" option. This will make visible a "Keep identity" checkbox. After checking that all you need to do is map your source key column to the target Identity key column, and it should work.

In my situation, this worked with a local source Access MDB file and a remote target SQL Server 2005 database connected through SQL authentication; in both cases the Connection Managers used OLE DB connections, and used the OLE DB source and target in the data flow.

Here is the original post:

After several happy months of *not* working with SSIS, I returned to the tool today to help me with a medium-complexity data migration. It hasn't even been one day, and already I'm facing a near-show-stopping limitation that's going to force me to either abandon SSIS or implement some kind of ugly hack. I'm starting remember why I was so happy to put SSIS in my rear view mirror after spending several months fighting with it on a previous project.

The problem I have today is transferring a table from a legacy database where I want to keep the original numeric PK values when I insert into the new destination table, which uses an IDENTITY column for the PK. The only way to do this is to use the SQL Server Destination, which only works in a bulk insert fashion (which is just so stupid--why can't we have a SQL Server-aware destination that is not limited to bulk insert?); bulk insert is no good to me because I'm not inserting into a local database connected via Windows authentication (another truly stupid limitation). The OLE DB destination sees an Identity column as "read only."

I found a blog post elsewhere lamenting this sorry state of affairs, with the suggested workaround of inserting first into a holding table and then calling a stored procedure that moves the rows from the holding table to the real table. This might be the way I end up going--IF I stay with SSIS for this project. I might have to sleep on that. I hate to walk away from SSIS, though, because I've already invested a day getting this far into my project, and if I go to a custom-coded solution I'm going to lose all of the convenient bootstrapping features of SSIS.

Aaarrgghhhh! I hope by now people at Microsoft have realized how poorly SSIS has been received (from my reading of the blogs and forums, anyway). It's not that it doesn't do some things pretty well, but the flaws it does have (don't get me started) are *really* annoying. Maybe my problem is that I just keep trying to use SSIS for things just outside of it's envisioned sweet spot... I hope someone at Microsoft has a master plan to stop SSIS from sucking. We need a tool like this that doesn't suck and that doesn't cost huge amounts of money in licensing fees.

I feel better now. I guess I better get to hacking...then I can take a shower.

Dan

UPDATE:
In case you were wondering whether you could use a pair of Execute SQL tasks to call SET IDENTITY_INSERT before and after the data flow task, it does not appear so. I tried this and received the error "User does not have permission to write to this column" on the first attempted insert.