What I wanted to do is to get the newly created identity of a row so that I can insert it to the main data set in data flow. I'm not even sure if there is even a much better design to achieve this. I've rummaged the internet but everything I got were all about Execute SQL Task.
Rather than using the OLE DB Command to get the identity column value (which is going to be significantly slower than inserting through an OLE DB Destination), have you considered generating your own keys in the data flow? There's an example here: http://www.ssistalk.com/2007/02/20/generating-surrogate-keys/ and here: http://www.sqlis.com/37.aspx, plus numerous posts on the forum.|||
Hi jwelch. I will be perusing those sites soon. Regarding key generation, I'm relying on IDENTITY columns so I'm afraid this is not an option for me unless those sites will prove otherwise. Thanks for the reply
|||I discovered that this is not the most optimized way of achieving my goal so I abandoned this one. Unfortunately, I encountered another showstopper which you can find more about here --> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1877437&SiteID=1|||
u_r_twisted wrote:
Regarding key generation, I'm relying on IDENTITY columns so I'm afraid this is not an option for me unless those sites will prove otherwise. Thanks for the reply
Gosh, I HATE identity columns. They are a pain in the rear to work with.... Nice for some things, but when doing ETL they stink. When you look at the link at my site above (ssistalk.com) that John linked to, I have a small discussion on why using identity columns is not preferred.
No comments:
Post a Comment