1

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.

Jens
  • 3,249
  • 2
  • 25
  • 42

1 Answers1

0

probably permission errors

Quick guess:

run GRANT EXECUTE ON xp_cmdshell TO shell

and maybe EXECUTE AS login = 'shell' instead of user

Diego
  • 34,802
  • 21
  • 91
  • 134
  • Execute as login doesn't seem to be working for this user, but I am currently using execute as user, which does work. Shell user already had permissions to execute xp_cmdshell – Jens Apr 12 '12 at 10:10
  • I think it has to be at a login level to work. can you try that? Maybe the user shell is mapped to a login with a different name – Diego Apr 12 '12 at 10:17
  • Logged in on my sql server with my shell user and re-executed the query. It ran as expected. Calling the stored procedure still left me with the timeout tho.. – Jens Apr 12 '12 at 11:09
  • It's mapped to a shell user specificly for this. The user has owner rights on the needed database and public rights on the master. ps: I very much appreciate you taking the time to look into this. – Jens Apr 12 '12 at 11:58
  • ok, I would try them: 1) running a command other than DTEXEC (like dir c:\) to see if the problem is with the dtexec command. 2) run it with the sa user to see if its a permission issue – Diego Apr 12 '12 at 12:00
  • Once again same results... I don't think this is a permission issue. It almost feels like there's a deadlock at hand here... but then again, I see no reason for having one. – Jens Apr 12 '12 at 12:47
  • The script runs some stored procedures and writes a message with the data from those. It seems the first connection to my SQL server is causing a timeout... but once again, only if the dtsx was called from a stored procedure, not if called from a stand alone query. - and in both cases the same connection string is used - – Jens Apr 12 '12 at 12:57
  • very weird indeed. I don't have a proper answer other than try several random things. Among them I would say to disable everything on the package to see if the timeout is calling the package or actually executing it. I also would try to run the proc that calls DTEXEC with sa login. Third, you said that it runs from a stand alone query but the example you provided doesn't insert data into the #SSISResult table. I would try that too. – Diego Apr 12 '12 at 13:03
  • I've accepted your answer as the answer since it suddenly started working. Not sure why, not sure how, just that it does. Thanks for your help :) – Jens Apr 12 '12 at 13:09