February 28, 2011 at 7:10 pm
Hi,
I'm loading tables (1 package per table, managed by a parent package) and need to be able to rollback all changes if any 1 child package fails.
I've tried the inbuilt transaction management with the parent package transaction set to required, isolationlevel read commited and the execute package task transaction set to Supported, and the child package set to required.
This works when I have my OLE DST set not to validate external metadata but I would rather be validating this.
I had configured manual transactions using Execute SQL tasks but I found that the transactions begun in the parent did not include the transaction in the child. I.e. when the child package committed its transaction then the parent rolled back its transaction the child data remained in the database.
Is there no other option but to use in built transactions in order to encapsulate a package hierarchy in a transaction and thus no choice but to not validate external metadata?
March 1, 2011 at 3:30 am
What is the exact reason that Validate External Metadata has to be set to false. I don't see the connection with transactions. Do you get any errors or warnings?
The reason why encapsulating a transaction with SQL statements doesn't work is because they do not use the same connection.
(the same connection manager is not the same as the same connection)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 1, 2011 at 3:58 pm
I'm setting ValidateExternalMetadata to false based on advice from relevant articles. I can't for the life of me find the ones I was looking at 4 weeks ago, but I found this one:
Basically I have a package loading each of my tables and each works on the truncate/reload principle.
I suppose if the only effect of setting validate external metadata to false is that errors occur at run time (if any) rather than at validation, then I can live with that.
March 1, 2011 at 11:23 pm
I've discovered the effects of having validateExternalMetadata set to false in the event of an error.
In this case trimming some char values, for insertion into varchars at the destination, caused some leading spaces in the second part of a composite primary key to be dropped thus violating the uniqueness of my primary keys.
The error with transactions turned off and validateExternalMetadata set to true is this:
[OLE_DST_xxx[765]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "The statement has been terminated.".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Violation of PRIMARY KEY constraint 'PK_xxx'. Cannot insert duplicate key in object 'dbo.xxx.".
The error with transactions turned on and validateExternalMetadata set to false is this:
[OLE_DST_xxx [765]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support. ".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Location: pageref.cpp:922
Expression: IS_OFF (BUF_MINLOGGED, m_buf->bstat) || pageModifyType != PageModifyType_Contents || GetPagePtr ()->IsTextPage ()
SPID: 122
Process ID: 1876".
Which is entirely unhelpful.
I need the system to cope with the possibility of such leading spaces (considered invalid by the business yet allowed via the front end) so I built in a Derived Column to concatenate the two primary key fields after the trims are performed (it also adds a boolean column which defaults to false), I added a sort based on the concatenation column, put in a script component to store the previous value in the dataflow and see if it is the same as the current. It updates the boolean column saying whether the row has a unique key and I added a conditional split to only send unique rows to the destination, and send duplicates to error (just a row count with a dataviewer at the moment).
All of this works fine and the row that is the culprit is successfully diverted to error. the remainder rows continue hapily on to the destination where it succeeds. But only if I have transactions off and validateexternalmetadata set to true.
When I switch them to on and false, it fails.
I then deleted the composite primary key from the destination table and it worked.
Anyway to avoid having to remove my primary key?
March 1, 2011 at 11:50 pm
sam.dahl (3/1/2011)
I'm setting ValidateExternalMetadata to false based on advice from relevant articles. I can't for the life of me find the ones I was looking at 4 weeks ago, but I found this one:Basically I have a package loading each of my tables and each works on the truncate/reload principle.
I suppose if the only effect of setting validate external metadata to false is that errors occur at run time (if any) rather than at validation, then I can live with that.
That is interesting. The purpose of ValidateExternalMetadata is that you don't run into errors during design time or during validation when there are objects that are only initialized during runtime, but apparently it can cause conflicts with the transaction manager. That's quite a bummer...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 1, 2011 at 11:53 pm
sam.dahl (3/1/2011)
In this case trimming some char values, for insertion into varchars at the destination, caused some leading spaces in the second part of a composite primary key to be dropped thus violating the uniqueness of my primary keys....
I need the system to cope with the possibility of such leading spaces (considered invalid by the business yet allowed via the front end) so I built in a Derived Column to concatenate the two primary key fields after the trims are performed (it also adds a boolean column which defaults to false), I added a sort based on the concatenation column, put in a script component to store the previous value in the dataflow and see if it is the same as the current. It updates the boolean column saying whether the row has a unique key and I added a conditional split to only send unique rows to the destination, and send duplicates to error (just a row count with a dataviewer at the moment).
All of this works fine and the row that is the culprit is successfully diverted to error. the remainder rows continue hapily on to the destination where it succeeds. But only if I have transactions off and validateexternalmetadata set to true.
When I switch them to on and false, it fails.
I then deleted the composite primary key from the destination table and it worked.
Anyway to avoid having to remove my primary key?
I would suggest creating a better primary key. Possible a surrogate key, instead of concatenating two fields.
As natural key you have the two seperate fields, which you can use seperately in a lookup component. You need to decide if you are going to keep the leading spaces, or drop them. But you can't have a difference in the destination table and in the SSIS pipeline.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 2, 2011 at 3:28 am
That's the thing. It's the primary key in the source and it should remain as such in the destination. The entry with leading spaces is invalid and we are quite happy to remove it with logic that catches such entries as duplicates and sends them to an error output. Only valid, unique entries should go to the destination.
The logic I described above achieves this and I've verified it by turning off transactions, turning on validate external metadata and running the package. It succeeds, the data is unique it is entered in to the table with primary key constraint.
I can turn on the transactions, set validate external metadata as true and run it with everything exactly the same and it fails.
The fact that I can then remove the primary key, run the package again with transactions and validate external metadata set as true, and it succeeds, then run a SELECT DISTINCT query on the 2 primary key fields in my freshly created table, and get the same number of records as expected, tells me that the data actually inserted via the OLE DST is correctly unique but that the validate external metadata is somehow flagging it incorrectly as a primary key violation.
I have two more things to try when I get to work tomorrow:
1. leave transactions off whilst setting validate external metadata as false and seeing what effect that has on the errors I receive. I expect nothing will change, but it will verify that it's not transaction related.
2. I can perform the duplicate removal in an earlier data flow, persist my unique data to the second DFT and then try and load it to the destination with primary key intact. I expect the result to be interesting.
March 2, 2011 at 3:36 am
If the conflict with transactions through the DTC still persists, you can try to create your own transactions by placing an Execute SQL Task with BEGIN TRAN at the beginning of the control flow and an Execute SQL Task with COMMIT at the end of the control flow. Make sure the property RetainSameConnection of the connection manager is set to TRUE.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 2, 2011 at 4:17 am
Yeah, that's where I started.
I initially had transactions managed via DTC then I found that I couldn't get the truncate update process to work without causing a lock (unless I turned off validateexternalmetadata).
So i implemented manual transaction control as you describe with the execute SQL statements.
Then I found that the packages when run in a parent package hierarchy did not participate in the parents transactions and so I was getting locks with my manual auditing logging (inserts and updates; across hierarchy levels), and also rolling back a parent did not rollback any successfully committed children packages which means partial updates would occur in the event of a failure (hence my original post).
So I went back to the inbuilt transaction management (DTC) and sucked it up and turned off validateExternalMetadata.
And then I encountered the issue that prompted my 3rd post in this thread...
So I'm fresh out of options, our parent package hierarchy and auditing configuration architecture is pretty much locked in at this point.
I really do appreciate your help but it's looking like i'm going to have to continue with the inbuilt transaction management, turn off validation, and possibly remove my primary key (it is a staging table at this point at least). We'll see what the morning brings 🙂
March 2, 2011 at 3:47 pm
sam.dahl (3/2/2011)
I have two more things to try when I get to work tomorrow:1. leave transactions off whilst setting validate external metadata as false and seeing what effect that has on the errors I receive. I expect nothing will change, but it will verify that it's not transaction related.
2. I can perform the duplicate removal in an earlier data flow, persist my unique data to the second DFT and then try and load it to the destination with primary key intact. I expect the result to be interesting.
I was surprised by the results.
For number 1, I added my primary key back to the table and set transactions off, leaving validate external metadata as false. And it worked!
For number 2, I turned transactions back on. I found that even after removing duplicates in an earlier data flow and persisting only unique data via a RAW file to the data flow that writes to table I still received an error.
Taking this a step further, I added a clause to the initial SELECT statement to exclude the duplicate row (and tested it in SSMS). This still failed.
I am stumped. :crazy:
March 2, 2011 at 5:03 pm
Update:
We finally got the source system data cleaned up and there is no longer invalid data but loading the destination table with the transactions on and validation off still failed.
With transactions off and validation on it works.
Problem is we need transactions. I'm going with removing the primary key.
March 2, 2011 at 7:41 pm
Ok, after some more investigation:
Transactions(DTC): ON
ValidateExternalMetada: FALSE
Primary key on destination.
I can load up to 9892 rows with no problem.
SELECT TOP 9892 A,B
FROM X
ORDER BY A,B
Succeeds.
SELECT TOP 9893 A,B
FROM X
ORDER BY A,B
Fails.
SELECT TOP 9892 A,B
FROM X
WHERE A > 'abcd' --This removes about the first 900 rows in the table.
ORDER BY A,B
Succeeds.
SELECT TOP 9893 A,B
FROM X
WHERE A > 'abcd' --This removes about the first 900 rows in the table.
ORDER BY A,B
Fails.
However loading a table that is exactly the same structurally completes successfully for all 10,706 rows (but I swear it wasn't working either earlier this morning). EDIT: It wasn't working, it worked this time because I had transactions turned off.
EDIT2:
The second table that is structurally the same with almost identical data is also failing at 9893 rows.
However, the table with 60,000 rows completes with no issue. The same package template is used for each table just changes to the source and destination table.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply