March 17, 2004 at 7:14 am
Hi All,
When I execute a DTS package under the security context of the SQLProxyAccount the import(from an excel file into a sql table) fails with the following message but under my context(sa) it works from the enterprise manager. The SQLProxyAccount is a domain account which definitely has access to the excel file.Any ideas?
output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: Delete from Table [Compathome].[dbo].[tblImportProducts] Step
DTSRun OnFinish: Delete from Table [Compathome].[dbo].[tblImportProducts] Step
DTSRun OnStart: Copy Data from Sheet1$ to [Compathome].[dbo].[tblImportProducts] Step
DTSRun OnError: Copy Data from Sheet1$ to [Compathome].[dbo].[tblImportProducts] Step, Error = -2147467259 (80004005)
Error string: Unspecified error
Error source: Microsoft JET Database Engine
Help file:
Help context: 5000000
Error Detail Records:
Error: -2147467259 (80004005); Provider Error: 0 (0)
Error string: Unspecified error
Error source: Microsoft JET Database Engine
Help file:
Help context: 5000000
DTSRun OnFinish: Copy Data from Sheet1$ to [Compathome].[dbo].[tblImportProducts] Step
DTSRun: Package execution complete.
NULL
March 17, 2004 at 8:23 am
This may be your problem. Check to see if there are more than 8 columns. Another workaround would be to set them all to text.
Hope this at least gives you a direction.
http://www.kbalertz.com/redir.aspx?kbNumber=236605
March 17, 2004 at 8:36 am
It works under my security context, which means there is nothing wrong with the file or the packge. I am a sa on the server. There is something else which is causing this to happen.
March 17, 2004 at 8:41 am
Does the proxy account have the proper rights to both the database and the SQL directory? It will need to be able to run DTSRUN.EXE.
As a TEST only, try setting the proxy account as a local administrator on the box. Remove that immediately after testing though.
March 17, 2004 at 9:15 am
It is able to run dtsrun.exe as the first step in the package finishes successfully if you see the o/p I attached. The second step which copies the data from an excel file into the table fails.
March 18, 2004 at 6:16 am
Where are you executing the DTS package from when you use the SQLProxyAccount?
In your DTS package, if you are referencing the excel file with a full path and drive letter instead of UNC convention, then whereever you call the DTS package from is where it is going to try to grab the excel file. So in your DTS package if your path and file is specified as "D:\excel\file.xls" instead of \\hostname\d$\excel\files.xls then it will try to get the excel file from the client machine that the DTS package is being called from and the file may not exist here.
I had this happen when I had a client machine execute a DTS package on the SQL server. I had referenced the file in the DTS package with a drive letter and when the DTS package executred, it looked for that file on the drive of the client machine not the server.
Could this be the issue?
Hope that makes sense.
March 18, 2004 at 6:22 am
No, I use the Pull UNC name to grab the file. It executes under the context of an SA account. One more thing it executes when executed from the Enterprise manager also when logged onto the box as SQLProxy account.
It only fails when executed from Query analyser or from a client application.
March 18, 2004 at 6:35 am
I had this exact same thing happen and it was because the file was being accessed with the logon credentials of the client (both cleint app and query analyser). It worked fine using EM but would fail with QA. Can't remember 100% why EM worked and QA failed, but I think it was because through EM, the DTS package used the NT logon credentials but through QA it tried to use the SQL logon ID as the NT credentials for the machine with the file - it was something like that. Through the client, it definitely tried to use the clients NT logon credentials.
Could you be having the same issue?
March 18, 2004 at 6:39 am
In your DTS package, can you return the user id that it thinks it is using and confirm that that user has permissions to the file?
March 18, 2004 at 7:06 am
I remember why my DTS package was failing for me under QA.
I was starting QA from EM and it was connecting me to the DB as the SQL logon that EM was invoked in (for example sa). When the DTS package tried to access the file, there was no NT logon ID to pass to the server and the logon failed for the user ''. When I started a new QA session and specified to connect with my NT logon, the DTS package worked fine because my NT logon had permissions on the server with the file the DTS package was trying to access.
March 18, 2004 at 9:36 am
I am connecting in both EM and QA using the same NT account so that is not an issue.
From EM the DTS package runs on the local machine and from QA I think on server. With this in mind I made SQLProxyaccount a local Administrator on the Live SQL Box and then it worked.
I still don't understand as to what kind of permissions we need to give to this account on the live box other that local admin to make it work.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply