While I have learned a lot from this thread I am still basically confused about the issues involved.
.I wanted to INSERT a record in a parent table, get the Identity back and use it in a child table. Seems simple.
To my knowledge, mine would be the only process running that would update these tables. I was told that there is no guarantee, because the OLEDB provider could write the second destination row before the first, that the proper parent-child relationship would be generated as expected. It was recommended that I create my own variable in memory to hold the Identity value and use that in my SSIS package.
1. A simple example SSIS .dts example illustrating the approach of using a variable for identity would be helpful.
2. Suppose I actually had two processes updating these tables, running at the same time. Then it seems the "variable" method will also have its problems. Is there a final solution other than locking the tables involved prior to updating them or doing something crazy like using a GUID for the primary key!
3. We have done the type of parent-child inserts I originally described from t-sql for years without any apparent problems. (Maybe we were just lucky.) Is the entire issue simply a t-sql one or does SSIS add a layer of complexity beyond t-sql that needs to be addressed?
TIA,
Barkingdog
SSIS processes data in a different way than T-SQL. The "pipleine" processes differently than batch oriented SQL and differently than cursor based SQL. This is by design of course and has certain benefits and trade-offs, and requires a different way of implementing a solution.
Whether you want to characterize that as an additional "layer of complexity" or not is up to you. I guess if you now have differing options for processing data, that have different advantages and disadvantages, with different costs and benefits, where previously you had only one option , you might consider that more complexity.
I consider it more options - and glad to have 'em...
|||
This post includes examples of how to create surrogate keys using SSIS, effectively the same problem as you are trying to solve: http://sqljunkies.com/WebLog/sqlbi/archive/2005/05/30/15684.aspx
We do not have a packaged solution for highly parallel loads in this version, but we will be releasing a paper on this subject in coming months.
Donald
|||Thanks,
I will read through the paper.
I am starting to think that, because of parallesim in SSIS, that the sql uniqueidentifier may be a more suitable "primary key" for a table than the traditional Identity value (integer) used in the past.
But I do believe the problem relates to paralellism in SSIS. I don't think this problem would likely occur from a stored proc call that used the "insert a new record, get the identity value, populate the child" approach. (Though I guess it could happen if one had a server farm..... Why is everything so complicated?!)
TIA,
Barkingdog
|||It's not because of parallelism in SSIS. We have seen this same problem with inserting and immediately retrieving an identity over many years. Unfortunately, everyone thinks they can guarantee the result, and sooner or later they trip up because of some glitch. Unfortunately, because these values are your unique identifiers, such a trip up can be quite serious.
Read:
http://www.aspfaq.com/show.asp?id=2499
http://www.databasejournal.com/features/mssql/article.php/3307541
Donald
|||>> We have seen this same problem with inserting and immediately retrieving an identity over many years"
The problem I face is that we will be running a single program or SSIS package that is the only program which updates these tables. Nothing else will update them. There are no multiple users, no server farm. I can hear the developers saying that the "problem" mentioned can not happen in that of environemnt. Are they correct or can someone come up with a simple t-sql script that forces the problem to happen in any sql environment?
TIA,
Barkingdog
|||
>The problem I face is that we will be running a single program or SSIS package that is the only program which updates these tables. Nothing else will update them. There are no multiple users, no server farm.
The problem I face is that I have heard this many times from many users - and yet somewhere, somehow, somewhen, the world turns out to be not quite this tidy.
If you think this will work for you, I guess I can only say good luck. But I have an uneasy feeling that in a few months time we'll be reading the "how do I rekey all my child records?" post.
Donald
|||Thanks Donald. I will follow your sage advice!
I'm just preparing for the bruises\abuse I'm going to get from developers who will think I'm wrong and crazy on this point. I'm sure the URL's you listed will dull their bite.
Barkingdog
|||Here's what I have learned about this issue so far:
1. This issue has always existed. It is now aggravated by multi-processor systems, server farms, and the parellelism introduced in SSIS.
2. The best thought seems to be to generate one's own counter and use it instead of relying upon an Identity field.
3. While better to use SCOPE_IDENTITY than @.@.Identity or IDENT_CURRENT, even that will not guarantee a correct parent-child linkage. (The problem is if one t-sql operaiton starts before another their is no guarantee it will complete and commit before the other. Forget sequential processing.)
4. I think the only available way to get the proper linkage is to use GUID (from NEWID()) to generate the "identity" key for tables. (But that approach won't be quick for use in JOINS unless I index the GUIDs!)
In summary, I don't think there is a GOOD solution to thie fundamental issue yet. It was mentioned that MicroSoft is working on a best-practices document regarding surrogate keys generation . I can hardly wait for it to be released!
Barkingdog
|||Actually, I can tell you the technique I have used, and don't recollect ever having a problem with it:
1: ALL rtables must have a _natural_ key defined, and enforced as a unique index.
2: Now after inserting a row into an rtable that uses a system generated surrogate key, you can use the _natural key_ to find the row, therefore it is guaranteed to be the correct row, and retrieve whatever surrogate system generated key was inserted in that row. Obviously there is a performance price to pay to getting the right answer, but typically that's better than a fast wrong answer...
3:now you can use that _guaranteed correct_ surrogate key in further processing as needed.
It has been many years since I have built an rtable that did not enforce uiqueness based on a _natural_ key. It is the only way to go...
