CONCAT in Execute SQL Task

I am curious to know if there is a way to suppress or temporarily store event errors until the final one, then take all those errors and write to db field?

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.|||

scoobyjw wrote:

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