xp_cmdshell in SP called from SSIS

Question

Post reply

xp_cmdshell in SP called from SSIS

Raul Black

Old Hand

Points: 303
More actions
July 18, 2007 at 6:03 pm

#153688

I'm experiencing an SSIS behavior problem that confuses me. I have reduced this problem to its simplest form by creating the following SP that calls the command shell and then raises an error.
----------SP stored in SQL 2005
create procedure stpTest
as
declare @strQuery varchar(64)
declare @errorMessage varchar(512)
SET @strQuery = 'dir E:\Test'
EXEC master..xp_cmdshell @strQuery
SET @errorMessage = '***ERROR*** in stpTest: '
RAISERROR (@ErrorMessage, 12, 1)
GO
----------------------end of SP

I use the following SQL command to call the SP:
-----------calling command
EXEC stpTest
--------------------------------
The error is raised when the SP is called from a Management Studio query editor but not when it is called from an Execute SQL Task of SSIS. In SSIS, it fails to raise an error event, instead ending with a "successful" result. Interestingly, if I pull the "EXEC" command out of the stored procedure, SSIS behaves properly and raises an error event. I've tried this in numerous configurations, and have only found a few where I can raise a user defined error after using xp_cmdshell in SSIS.
I appreciate any tips, comments, or suggestions that can help me to better understand this behavior.

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

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

Raul Black Old Hand Points: 303 More actions · Answer 1

Just to follow up the above post. I reduced the SP to:

------------------------------------------------

CREATE PROCEDURE

[dbo].[stpTest]

AS

DECLARE @strQuery VARCHAR(64)

SET @strQuery = 'dir E:\Test'

EXEC master..xp_cmdshell @strQuery

GO

--------------------------------

I then created an Execute SQL Task in SSIS with the following code:

--------------------------------

EXECUTE dbo.stpTest

RAISERROR

('Error in the SSIS Execute SQL task, 12, 1)

---------------------------------

Because I called xp_cmdshell in the SP before explicitly raising an error in the SSIS task, SSIS does not raise an error event. Right now, I'm wondering whether this is a bug in SSIS or if there is something wrong in my environment (bad installation etc.)?

Thank you

Raul Black Old Hand Points: 303 More actions · Answer 2

The weird thing to me is that by creating a temp table #temp in my SP and inserting the command output into it, it makes the problem go away.

INSERT

INTO #Test (output1) EXEC @CmdResult = master..xp_cmdshell @strQuery

I can now do a RAISERROR and SSIS will recognize it.

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

I posted about this recently. The execute SQL component in SSIS will not error when running a procedure if the procedure returns a result set before the error is raised. I am not sure why, but this is the case. I suspect that if you use the recordset (with a loop container or something), you can pick up the error once the recordset has been used, but I have not tested this.

You need to ensure your error is raised before the recordset is returned.

On a side note, using xp_cmdshell to determine if a directory has any contents in an SSIS package is something that I would suggest as not good practice. I would not want to questions your reasons for doing this, but you have several objects in the toolbox that are for file system operations.

Raul Black Old Hand Points: 303 More actions · Answer 4

Michael:

Thanks for your explanation

I used that code to demonstrate the behavior of xp_cmdshell in SSIS, but I'm not using that particular structure in my SSIS package. However, I'm thinking this problem could raise havoc with people's systems who have migrated old SP's to 2005. They could be getting errors and be totally unaware that there is a problem.