Trap BCP error from Execute SQL Task

Question

Post reply

Trap BCP error from Execute SQL Task

daustinash

SSC Eights!

Points: 859
More actions
January 2, 2008 at 11:07 am

#377762

This is a pretty good one. I've been trying to figure it out all day.
I call a stored proc from an Execute SQL Task that uses an extended procedure call to issue a bcp command. The whole try/catch block looks like this:
BEGIN TRY
EXEC @ret = master..xp_cmdshell @v_bcpcmd
IF @ret <> 0
BEGIN
SELECT 1/0
RAISERROR ('bcp failed.', 16, 1)
END
END TRY
BEGIN CATCH
SELECT 1/0
END CATCH
I have SELECT 1/0 in there just to make absolutely sure a hard error is being thrown. The problem is that once the xp_cmdshell call is executed, SSIS will not pick up on any error thrown from the sp. If I move the SELECT 1/0 to the line before the EXEC xp_cmdshell or comment out the xp_cmdshell call, the error bubbles back up to the Execute SQL Task as expected.
Is this a context switching issue with the msdb database (home of the xp_cmdshell)?
Thanks
dnash

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

I have seen this before.

The ExecuteSQL task sees things happening in order.

You probably have the ResultSet property set to "None". So, SSIS executes the package and assumes it is ok to move on after the result set has been successfully returned.

If I remember correctly, changing the ResultSet property to "Full result set" will force SSIS to wait until everything is done and the error being thrown after the result set is returned will generate an error.

Try that - if it does not work, post back here and I will go look in some old projects to find one that had this issue. I know there was a solution - I am just not 100% sure this is it.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 2

I looked through my old projects and came across the first one in which I had similar behavior. Unfortunately, my suggestion will not work for your situation.

My problem was that I had a query that would return some rows and then error. I discovered that SSIS does not report this error unless you actually use the recordset that received the error and get through all of the rows before the error. You have a slightly different issue in that the error comes completely after the recordset being returned. This is going to make it a bit more complicated to solve.

I would suggest not returning the recordset at all. It is coming form the xp_CmdShell you are running. If you return this into a temp table, you will not have a result set returned before your error is raised.

I cannot test this in my environment right now, but it should move you closer to a solution.

SET NOCOUNT ON

CREATE TABLE #tmpOut (COut VARCHAR(255))

BEGIN TRY

INSERT #tmpOut (COut)

EXEC @ret = master..xp_cmdshell @v_bcpcmd

IF @ret <> 0

BEGIN

SELECT 1/0

RAISERROR ('bcp failed.', 16, 1)

END

END TRY

BEGIN CATCH

SELECT 1/0

END CATCH

daustinash SSC Eights! Points: 859 More actions · Answer 3

Thanks Michael. I had just tried the first solution and you're right. No workee. I'll take a look at solution #2 and see if it provides some insight.

Stay tuned.

Thanks.

dn