0

I'm new to a project and created a new SSIS package. When I execute the package by double-clicking it, it works. When I type into the shell:

dtexec /F "D:\path\to\my\ssis\package\mypackage.dtsx" /SET \Package.Variables[User::MyVariable].Properties[Value];"10"

It works also. But when I run my Stored Procedure which has the following code:

EXEC xp_cmdshell 'dtexec /F "D:\path\to\my\ssis\package\mypackage.dtsx" /SET \Package.Variables[User::MyVariable].Properties[Value];"10"'

It fails. The error has the source: mypackage Connectio manager "my connection manager" Desc: "Login failed for user 'myUser'." 'myUser' has rights to execute xp_cmdshell. It is the proxy user. Oddly enough, I can execute other packages without any problems, just this one doesn't work. Any idea what I'm missing? (BTW: all Packages have DontSaveSensitive protection level)

Here the full error message, any ideas?

Elapsed:  0.296 seconds
Finished: 10:33:44
Started:  10:33:44
DTExec: The package execution returned DTSER_FAILURE (1).
End Warning    
d in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded,     but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specifie
Source: csv_v1_import_package 
Code: 0x80019002
Warning: 2013-07-18 10:33:44.60
End Error
Description: Failed to acquire connection "My Connection Manager". Connection may not be configured correctly     or you may not have the right permissions on this connection.
Source: Update MyTable SQL Code Execute SQL Task
Code: 0xC00291EC
Error: 2013-07-18 10:33:44.60
End Error
An OLE DB record is available.  Source: "Microsoft OLE DB Provider for SQL Server"  Hresult:     0x80040E4D  Description: "Login failed for user 'MY_SERVER\username'.".
Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code:     0x80040E4D.
Source: csv_v1_import_package Connection manager "My Connection Manager"
Code: 0xC0202009
Error: 2013-07-18 10:33:44.60
Started:  10:33:44
NULL
Copyright (C) Microsoft Corporation 2010. All rights reserved.
Version 10.50.4000.0 for 64-bit
Microsoft (R) SQL Server Execute Package Utility
BaseBallBatBoy
  • 685
  • 2
  • 12
  • 25
  • I found my problem. In the package I actually use a data source as my connection manager (yellow container with 4 blue arrows). I switched to use a real connection manager (yellow container) and then it works all fine... – BaseBallBatBoy Jul 18 '13 at 19:44
  • possible duplicate of [Passing a variable through DTEXEC with xp\_cmdshell (SQL Server 2008)](http://stackoverflow.com/questions/10982780/passing-a-variable-through-dtexec-with-xp-cmdshell-sql-server-2008) – billinkc Oct 02 '13 at 14:28

1 Answers1

0

xp_cmdshell only allows for one set of double quoted parameters to be passed.

References

Community
  • 1
  • 1
billinkc
  • 59,250
  • 9
  • 102
  • 159
  • I have anohter package myotherpackage.dtsx which I have an SP for just like the one above and there it works with no issues (I mean: it's all the same, only the package name is anohter). So I think the double quoted parameters can't be it. Any thoughts on this? – BaseBallBatBoy Jul 16 '13 at 15:18