I have a package that uses an event error to store msgs into a field. But, I end up getting several errors that will overwrite my db field each time. Only about two out of 10 are relevent to my problem...
I just need to know if there is a way to suppress the number of event errors that come over , or concat them into one event, per event that occurs? Right now when I hit an error, I end up getting 10 error messages that follow, so my OnError event gets triggered 10 times..
Jason
One way to handle this may be to count the errors using a script in your event handler. Update a variable until you reach the last error you wish to handle, then enable the behaviour you wish to execute.
Donald Farmer
Group Program Manager
SQL Server Integration Services
|||Thanks Donald, how do I determine when the final error occurs in a failure?
So, let's say I have an object that fails, I get 5 onerror event called msgs, how do I know that 5 (or whatever number) is my last error msg for the failure?|||
Is it possible to concat in a Execute SQL Task - T-SQL statement? I tried to do this:
UPDATE ETL_Transactions SET LogDetails = LogDetails + ?, TransactionStatus = 'Failed' WHERE TransactionID = ?
where LogDetails is the field I want to concat with another parameter. However, this doesn't work! IS it supposed to, or am I missing something?
|||scoobyjw wrote:
Is it possible to concat in a Execute SQL Task - T-SQL statement? I tried to do this:
UPDATE ETL_Transactions SET LogDetails = LogDetails + ?, TransactionStatus = 'Failed' WHERE TransactionID = ?
where LogDetails is the field I want to concat with another parameter. However, this doesn't work! IS it supposed to, or am I missing something?
Jason,
Why not try building the SQL statement using a property expression on the SQLStatementSource property?
-Jamie|||The problem is that when this query runs [UPDATE ETL_Transactions SET LogDetails = LogDetails + ?, TransactionStatus = 'Failed' WHERE TransactionID = ?]
and LogDetails tries to concat (LogDetails + ?), it errors out because LogDetails has a null value (as it should the first time around)... It doesn't like setting null values in the query. Is there a way to say:
if Not Null(SET LogDetails = LogDetails + ?)
else LogDetails = ?
? Sorry about the pseudo code, I am not an expert at SQL.|||
The problem is that when this query runs [UPDATE ETL_Transactions SET LogDetails = LogDetails + ?, TransactionStatus = 'Failed' WHERE TransactionID = ?] and LogDetails tries to concat (LogDetails + ?), it errors out because LogDetails has a null value (as it should the first time around)... It doesn't like setting null values in the query. Is there a way to say:
if Not Null(SET LogDetails = LogDetails + ?)
else LogDetails = ?? Sorry about the pseudo code, I am not an expert at SQL.
Yeah, try this:
[UPDATE ETL_Transactions SET LogDetails = COALESCE(LogDetails, '') + ?, TransactionStatus = 'Failed' WHERE TransactionID = ?]
I really think you should look at using a property expression tho
-Jamie
