August 16, 2016 at 5:18 am
Hello all, I have a problem with a DTSX package. it is supposed to be started via a stored procedure that is triggered from a different programm
It used to work well untill there was a new SQL Server 2012 set up (it used to be on 2008) so when all was moved it stopped working. If I try to execute this from CMD it all works well, but when done from SQL Studio via Stored Procedure its not working
The sp does just this:
SET @LOC='D:\Transfer\ExpUD1.bat ' + (Select distinct partname from TDATAMAPFiltered)
EXEC xp_CMDShell @LOC
and the batch is nothing else than:
"C:\Program Files (x86)\Microsoft SQL Server\110\DTS\binn\DTExec.exe" /F D:\Transfer\TransferUD1.dtsx /decrypt password
What it does it just updates a Excel sheet with the data from the table. But we get this error (when started from the SQL Management Studio):
NULL
C:\Windows\system32>"C:\Program Files (x86)\Microsoft SQL Server\110\DTS\binn\DTExec.exe" /F D:\Transfer\TransferUD1.dtsx /decrypt password
Microsoft (R) SQL Server Execute Package Utility
Version 11.0.6020.0 for 32-bit
Copyright (C) Microsoft Corporation. All rights reserved.
NULL
Started: 12:55:49 PM
Error: 2016-08-16 12:55:50.69
Code: 0xC0202009
Source: TransferMappings Connection manager "DestinationConnectionExcel"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error".
End Error
Error: 2016-08-16 12:55:50.71
Code: 0xC00291EC
Source: Drop table(s) SQL Task 1 Execute SQL Task
Description: Failed to acquire connection "DestinationConnectionExcel". Connection may not be configured correctly or you may not have the right permissions on this connection.
End Error
Error: 2016-08-16 12:55:50.71
Code: 0xC0202009
Source: TransferMappings Connection manager "DestinationConnectionExcel"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error".
End Error
Error: 2016-08-16 12:55:50.71
Code: 0xC00291EC
Source: Preparation SQL Task 1 Execute SQL Task
Description: Failed to acquire connection "DestinationConnectionExcel". Connection may not be configured correctly or you may not have the right permissions on this connection.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 12:55:49 PM
Finished: 12:55:50 PM
Elapsed: 0.797 seconds
NULL
I tried changing the delay but it does not work - I emphasize that it works when the batch is started from CMD
Thank you for your help
August 20, 2016 at 12:40 pm
One key difference between the tested methods appears to be the methods' security contexts. Replace your batch file with a test batch file that redirects WHOAMI.exe output to a text file (such as WHOAMI>>C:\whoranthis.txt). Run the same xp_cmdshell command, using both methods you have been testing, and compare the redirected text file (such as whoranthis.txt). If you still have the 2008 system, do the same test there.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply