The following issue has been bugging me for a while:
I have a dtsx-package for handling some interfacing. I call it in a stored procedure as seen below:
SET @v_statement = 'DTEXEC /FILE "' + @v_if_ssis_package + '" /SET \Package.Variables[User::message_number].Properties[Value];' + CAST(@i_message_number AS VARCHAR(20))
+ ' /ConfigFile "' + REPLACE(@v_if_ssis_package, '.dtsx', '.xml') + '"'
EXECUTE AS USER = 'shell';
INSERT INTO #SSISResult
EXEC @v_ssis_returncode = xp_cmdshell @v_statement;
The Shell user has, for as far as I know, everything in place to be able to run xp_cmdshell calls. Nevertheless I bump into following error while running my stored procedure:
Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 10:12:28 Error: 2012-04-12 10:12:59.11 Code: 0x00000000 Source: Script Task Script Description: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding...
Here's the thing which has been bugging me about this.. If I would run the exact same thing as a stand alone query (as seen below) it runs just fine. Any ideas on what's going nanners here?
EXECUTE AS USER = 'shell';
EXEC xp_cmdshell 'dtexec /FILE "C:\mypackage.dtsx" /SET \Package.Variables[User::message_number].Properties[Value];48644 /ConfigFile "C:\mypackage.xml"'
Thanks a bunch to anyone who can give me some pointers on this issue.
probably permission errors
Quick guess:
run GRANT EXECUTE ON xp_cmdshell TO shell
and maybe EXECUTE AS login = 'shell' instead of user