February 3, 2012 at 9:57 am
I have created a bat file to call SSIS package using 32 bit dtexec.exe file. it works fine when I double click on the batch file directly but when I call batch file from xp_cmd_shell it return an error.
Batch file contents.
"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe" /f "D:\SSIS\ICES\ICES.dtsx" /configfile "d:\ssis\ices\oracleConfig.dtsConfig"
when I run following command I get an error
EXEC master..xp_cmdshell 'D:\SSIS\ICES\ICES.BAT'
error :
An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "ORA-06413: Connection not open.
February 3, 2012 at 10:50 am
I would almost guarantee that you have a permissions issue. When you run xp_cmdshell the shell is opened using either the proxy account specified using sp_xp_cmdshell_proxy_account or the service account running sql server.
How are your connections set up in your SSIS package? Can you tell if the package is being opened? I'm not faimiliar with your specific error and particularly not familiar with connections to Oracle, but making several assumptions it sounds like it is an error from within the SSIS package, and your connection is trying to use AD authentication to connect to the oracle DB that sounds like what it would be.
Hope that helps
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
February 3, 2012 at 10:51 am
It could be a security issue. When you are executing xp_cmdshell it uses service account's credentials if you are sysadmin or any other windows credentials if proxy is configured.
Whatever account xp_cmdshell is using here does it have permisisons to do what is there in bat file ?
February 3, 2012 at 11:21 am
When I run the batch file. it works fine. I have stored Oracle username and password in the ssis config file and works fine but when I use xp_cmdshell command if failed on the oracle connection.
Yes, it uses the SQL Server Agent Service account which has no access on oracle server but don't you think that SSIS package should take the username and password from the SSIS config file and should work ?
February 3, 2012 at 1:47 pm
One other possibility is your dtsConfig file.
d:\ssis\ices\oracleConfig.dtsConfig
When you run the xp_cmdshell its looking at the harddrive of your SQL Server. When you run the bat file its run from whereever you start. Probably your workstation. What is the config file configuring?
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply