April 22, 2016 at 12:02 pm
I'm having issues with a package that contains a script step. This calls the Microsoft.Office.Interop.Excell dll. It runs perfectly fine when executed in Data Tools, but fails when run in an Agent Job. I've logged into the server as the SQL Agent account and verified successful completion with Data Tools.
Research led me to a number of posts that suggest creating the following folders:
C:\Windows\SysWOW64\config\systemprofile\Desktop
C:\Windows\system32\config\systemprofile\Desktop
I've created these, but now, instead of outright failing, it appears as though the package will not properly execute. The service accoutn has Full control over these folders. I've enabled logging and it appears to hang indefinitely after validating the script step:
OnPreExecute,ServerName,Domain\ServiceAccount,Import Data From Excel,{2E2DA110-CA41-4C44-9C31-5E72D6E7728E},{F6F9D85A-F22F-48BF-8FEF-94466231A42D},4/22/2016 1:56:52 PM,4/22/2016 1:56:52 PM,0,0x,
OnPreValidate,ServerName,Domain\ServiceAccount,Import Data From Excel,{2E2DA110-CA41-4C44-9C31-5E72D6E7728E},{F6F9D85A-F22F-48BF-8FEF-94466231A42D},4/22/2016 1:56:52 PM,4/22/2016 1:56:52 PM,0,0x,
OnPostValidate,ServerName,Domain\ServiceAccount,Import Data From Excel,{2E2DA110-CA41-4C44-9C31-5E72D6E7728E},{F6F9D85A-F22F-48BF-8FEF-94466231A42D},4/22/2016 1:56:52 PM,4/22/2016 1:56:52 PM,0,0x,
This would lead me to suspect that there's something to these folders, but I'm at a loss here. The folders are owned by SYSTEM with the service account having Full Control. Any ideas?
April 22, 2016 at 12:16 pm
Yessir, both result in the same
April 22, 2016 at 1:01 pm
Jeff Shurak (4/22/2016)
Yessir, both result in the same
Does the Agent History tab give you anything at all useful?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 22, 2016 at 2:09 pm
Unfortunately no, in the case of failure its
Source: Import Data From Excel Description: Exception has been thrown by the target of an invocation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 3:57:45 PM Finished: 3:57:46 PM Elapsed: 0.797 seconds. The package execution failed. The step failed
in the case of those Desktop folders, the agent job just runs forever because it can't execute that step.
April 22, 2016 at 2:14 pm
have you tried RDP to the server (using the Sql Server agent ID) and then running the package from a windows command line with the DTEXEC command?
or even on the server create a package with a script on it. you don't even have to save it, just create the script in a package
April 22, 2016 at 2:52 pm
April 22, 2016 at 7:11 pm
April 22, 2016 at 8:47 pm
DCOM maybe? see http://answers.flyppdevportal.com/MVC/Post/Thread/c7f52891-7920-480c-999e-183d8f1b6387?category=sqlintegrationservices for some possible issues
April 23, 2016 at 6:30 am
Every time I have had this issue it's all been down to permissions on folders. You may not have any folders in your SSIS package but if you have, your sql server agent (the one running the job) will need permissions on the folders you have used.
Worth a try?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply