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

Compilation Errors When Trying To Compile Package Body!

Hi,

I am receiving compilation errors when I am trying to compile a package body, but for the life of me I can figure out why!

Can anybody help? Its driving me insane!!

--============================================
--= PACKAGE BODY
--============================================
CREATE OR REPLACE PACKAGE BODY student_manager
AS

PROCEDURE insert_student
(i_SALUTATION IN VARCHAR2,
i_first_name IN VARCHAR2,
i_last_name IN VARCHAR2,
i_STREET_ADDRESS IN VARCHAR2,
i_ZIP IN VARCHAR2,
i_PHONE IN VARCHAR2,
i_EMPLOYER IN vARcHAR2,
i_REGISTRATION_DATE IN DATE);
IS
BEGIN
INSERT INTO student VALUES
(get_new_student_id,
i_SALUTATION,
i_first_name,
i_last_name,
i_STREET_ADDRESS,
i_ZIP,
i_PHONE,
i_EMPLOYER,
i_REGISTRATION_DATE,
user,
sysdate,
user,
sysdate);
END insert_student;

--============================================

PROCEDURE delete_student
(i_student_id IN student.student_id%TYPE)
IS
BEGIN
DELETE FROM student
WHERE student.student_id = i_student_id;

END delete_student;

--============================================

FUNCTION get_new_student_id

RETURN NUMBER
IS
v_seq_id student.student_id%TYPE;

BEGIN
SELECT student_id_seq.nextval
INTO v_seq_id
FROM dual;
RETURN v_seq_id;

END get_new_student_id;

END student_manager;
.

--============================================
-- ERRORS:
--============================================

Warning: Package Body created with compilation errors.

SQL> show errors
Errors for PACKAGE BODY STUDENT_MANAGER:

LINE/COL ERROR
--- --------------------
13/1 PLS-00103: Encountered the symbol "IS" when expecting one of the
following:
begin end function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> form
current cursor

34/1 PLS-00103: Encountered the symbol "PROCEDURE"
59/1 PLS-00103: Encountered the symbol "END" when expecting one of the
following:
begin function package pragma procedure formThere a just a couple of things I noticed, which you can try fixing-

1. Your statement- CREATE OR REPLACE PACKAGE BODY student_manager
AS
should read as-
CREATE OR REPLACE PACKAGE BODY student_manager is

2. You have a semicolon in the procedure insert_student before 'IS'
( i_REGISTRATION_DATE IN DATE);)
remove this semicolon and try.

--Shekhar Pendyala

Originally posted by iknownothing
Hi,

I am receiving compilation errors when I am trying to compile a package body, but for the life of me I can figure out why!

Can anybody help? Its driving me insane!!

--============================================
--= PACKAGE BODY
--============================================
CREATE OR REPLACE PACKAGE BODY student_manager
AS

PROCEDURE insert_student
(i_SALUTATION IN VARCHAR2,
i_first_name IN VARCHAR2,
i_last_name IN VARCHAR2,
i_STREET_ADDRESS IN VARCHAR2,
i_ZIP IN VARCHAR2,
i_PHONE IN VARCHAR2,
i_EMPLOYER IN vARcHAR2,
i_REGISTRATION_DATE IN DATE);
IS
BEGIN
INSERT INTO student VALUES
(get_new_student_id,
i_SALUTATION,
i_first_name,
i_last_name,
i_STREET_ADDRESS,
i_ZIP,
i_PHONE,
i_EMPLOYER,
i_REGISTRATION_DATE,
user,
sysdate,
user,
sysdate);
END insert_student;

--============================================

PROCEDURE delete_student
(i_student_id IN student.student_id%TYPE)
IS
BEGIN
DELETE FROM student
WHERE student.student_id = i_student_id;

END delete_student;

--============================================

FUNCTION get_new_student_id

RETURN NUMBER
IS
v_seq_id student.student_id%TYPE;

BEGIN
SELECT student_id_seq.nextval
INTO v_seq_id
FROM dual;
RETURN v_seq_id;

END get_new_student_id;

END student_manager;
.

--============================================
-- ERRORS:
--============================================

Warning: Package Body created with compilation errors.

SQL> show errors
Errors for PACKAGE BODY STUDENT_MANAGER:

LINE/COL ERROR
--- --------------------
13/1 PLS-00103: Encountered the symbol "IS" when expecting one of the
following:
begin end function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> form
current cursor

34/1 PLS-00103: Encountered the symbol "PROCEDURE"
59/1 PLS-00103: Encountered the symbol "END" when expecting one of the
following:
begin function package pragma procedure form|||Hi,

You need to put a forward slash '/' at the end of the package specification. The slash needs to be on a line by itself after the last "end;" in the package specification.