March 10, 2015 at 7:45 am
OK, got a strange problem for which resolution has been eluding me.
I have an SSIS package that loads multiple electronic data interchange files via a ForEach Loop. There is then a sequence container that executes several Execute SQL Tasks to parse the information into tables. The last Execute SQL Task in the sequence container is a stored procedure that populates the three primary tables that all subsequent processes depend on (shipment, order, and load level information).
A subsequent task calls a stored procedure which combines the three aforementioned tables and generates information that is ultimately used to load data into another system. For auditing purposes, we have been including the id fields from the shipment, order, and load tables. What is interesting is that auditing values end up not matching any value in the shipment, order, and load tables.
For instance, the final table says the data comes from shipment level record 3920, but there is no such record in the shipment table and the data in fact came from the 3921 shipment record. The data is correct, but it's as if the value initially generated by the insert into the shipment table changes before the data is finally committed.
All items in the control flow have an IsolationLevel of Serializable and TransactionOption of Supported and the identity fields on the shipment, order, and load tables are also primary keys.
While this isn't a showstopper and the problem appears to have been happening since even before I joined the organization, I am curious what is happening here.
Any assistance or advice would be appreciated.
Thank you.
March 10, 2015 at 10:08 am
Have you got any DELETES within your process or is it possible that an INSERT failed? In both cases you would see a jump in your IDENTITY values.
Regards
Lempster
March 10, 2015 at 10:47 am
For sure no deletes. There's no indication that the inserts failed as the data is in the ship, order, and load tables.
March 10, 2015 at 11:09 am
m_swetz (3/10/2015)
OK, got a strange problem for which resolution has been eluding me.I have an SSIS package that loads multiple electronic data interchange files via a ForEach Loop. There is then a sequence container that executes several Execute SQL Tasks to parse the information into tables. The last Execute SQL Task in the sequence container is a stored procedure that populates the three primary tables that all subsequent processes depend on (shipment, order, and load level information).
A subsequent task calls a stored procedure which combines the three aforementioned tables and generates information that is ultimately used to load data into another system. For auditing purposes, we have been including the id fields from the shipment, order, and load tables. What is interesting is that auditing values end up not matching any value in the shipment, order, and load tables.
For instance, the final table says the data comes from shipment level record 3920, but there is no such record in the shipment table and the data in fact came from the 3921 shipment record. The data is correct, but it's as if the value initially generated by the insert into the shipment table changes before the data is finally committed.
All items in the control flow have an IsolationLevel of Serializable and TransactionOption of Supported and the identity fields on the shipment, order, and load tables are also primary keys.
While this isn't a showstopper and the problem appears to have been happening since even before I joined the organization, I am curious what is happening here.
Any assistance or advice would be appreciated.
Thank you.
Looks to me like you need to check the logic that says which shipment level record the data came from. From your example, that would be the logic that returned the value of 3920.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
March 10, 2015 at 11:15 am
I have. When I run the procedure after the fact the correct ID is placed with the information. The other data fields on the record with the incorrect ID are consistent with the shipment level record. The only thing is off is the auditing value that does not correspond with any record in the original data table.
March 10, 2015 at 11:22 am
How are the auditing values generated?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 10, 2015 at 12:55 pm
There is a stored procedure that merges the ship, order, and load tables that does computations and concatenations of fields. This procedure also attempts to retain the identity fields. There is an execute SQL task that calls the stored procedure.
Is that what you were asking?
March 10, 2015 at 12:57 pm
m_swetz (3/10/2015)
There is a stored procedure that merges the ship, order, and load tables that does computations and concatenations of fields. This procedure also attempts to retain the identity fields. There is an execute SQL task that calls the stored procedure.Is that what you were asking?
Yes indeed. It sounds like the "attempts to retain the identity fields" bit might not be working correctly. Can you verify?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 11, 2015 at 3:03 am
m_swetz (3/10/2015)
There is a stored procedure that merges the ship, order, and load tables that does computations and concatenations of fields. This procedure also attempts to retain the identity fields. There is an execute SQL task that calls the stored procedure.Is that what you were asking?
Are you using SCOPE_IDENTITY() within that stored procedure and then assigning the result to a variable which is reused multiple times? I've been bitten by that before.
March 11, 2015 at 7:01 am
No, the identity field isn't passed to a variable. The first procedure populates the shipment, order, and load tables, all of which have identity fields. Later the second procedure includes the identity fields from the shipment, order, and load tables in the joins and the results are inserted into another table.
With the exception of the identity fields, all the other columns are consistent between the tables. It's almost as if initially ID values are assigned for the shipment, order, and load records, the second procedure runs, and the id values change when the transaction is committed. Given the serializable isolation level, I didn't think that was possible, and it may not be the case, but functionally that's what it looks like.
I think I'm going to try explicitly beginning and committing a transaction for the execution of the first procedure.
March 11, 2015 at 7:10 am
Are there any triggers on any of the target tables?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 11, 2015 at 7:26 am
No, no triggers.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply