0

We have a task where we need to automatically convert an excel file to a csv to prep it for loading into a SQL database. The developers built this process into a SSIS package. For the conversion, they initially tried to have a task in the SSIS package execute a VBscript to convert the file. When they were running this on there local machines, this worked correctly. When they ran the package manually through VS on the server, it ran correctly. When they ran the package manually via the Integration Catalog it ran correctly. We did this both as our accounts and as the service account and got the same results. However, when we scheduled it as a job it would hang on the part of the process that executed the VBScript. No errors, it would just hang until you killed the job.

The job was executing as the service account which has full admin access on the server, explicit full access to the share where the files are stored and converted (which is on the same server) and full admin access to SQL. The job owner is set to sa which uses the service account. And all the job does is execute the package from the integration catalog which works if you run it independently of a job. When we compared the ssisdb execution report for the manual run in the integration catalog using the service account to the job run they looked the same except the job hung on the conversion task and the other did not.

After spending some time trying to figure this out, the developers tried a different solution. They changed the conversion script from using VBScript to using C#. They ran the package from there local machine and once again the package worked. This time when they ran it manually on the server it failed. When we ran it from the integration catalog it failed and when we ran it from a job it failed.

The error we keep getting is "Create CSV file: Error: Exception has been thrown by the target of an invocation" After spending several hours looking into this error nothing suggested seems to be working.

We also tried these same solutions on a newly built server to make sure we weren't dealing with an odd configuration setting that could have been changed (It is a Dev server) and it still failed there.

At this point, we are pretty lost at what is happening. The first solution worked, but for some reason would never work as a job. The 2nd solution works everywhere except when ran on the server.

We are looking at some other solutions to try to get around this. The next thing may be trying to using powershell to convert the file, but not sure if that will bring us back to the same issue. Any suggestion you guys have will be greatly appreciated

Also, we are using SQL Server 2012 dev edition, VS 2012 pro, Windows Server 2012 R2

Dustin
  • 13
  • 2
  • It sounds like an issue where a path that is valid in one environment is not valid in another environment. Make sure you are using universal paths to every file referenced anywhere in the package. – Tab Alleman Sep 21 '16 at 13:35
  • When we execute it from our local machine (executing against the Dev server) it works. In the case of using the VBScript, it works everyway on the server except setup as a job. Everything is setup on the Dev , paths, files, etc and they are not changing at any point. Not sure I am following, let me know if I am missing something in what you are saying. Thanks. – Dustin Sep 21 '16 at 13:45
  • Also, there are no parameters on the package, everything is hardcoded at this point, so nothing to set when we schedule it. – Dustin Sep 21 '16 at 13:47
  • Yeah so what I'm saying is maybe there's a file path that exists on your Dev machine, and then when you move the package to another server, that file path either doesn't exist or can't be reached from that server. Maybe due to permissions. – Tab Alleman Sep 21 '16 at 14:09
  • Could be a variety of issues on why it doesn't work in agent http://stackoverflow.com/a/26122982/181965 http://stackoverflow.com/a/37870251/181965 We'd need to know much more about the solution to offer any realistic advice aka post a minimal reproduction of what they are doing – billinkc Sep 21 '16 at 15:09

1 Answers1

0

This might be because of a bug that Excel has when trying to run jobs (that use Excel) and no user is logged on a specific machine. This might affect also the excel library. The solution is to create the following 2 folders:

  • C:\Windows\SysWOW64\config\systemprofile\Desktop
  • C:\Windows\System32\config\systemprofile\Desktop

and then restart the machine. :)

(Edited to show that a restart is needed. thanks for Dustin for checking this)

ldobre
  • 361
  • 2
  • 6
  • if you have nothing better to do, could you try to restart the server ? I don't remember if it was needed or not, but we are talking about microsoft solutions so ... – ldobre Sep 21 '16 at 16:27
  • 1
    That fixed it. We got this working on the Dev server by doing this, but we had done so much I wanted to make sure this was the fix. Walked through the same steps on our QA server, testing the package after each one and confirmed that this was what fixed it. Thank you very much for your help, not sure we would have figured that out otherwise. – Dustin Sep 22 '16 at 17:13