February 6, 2015 at 1:59 pm
Hello All,
This is a weird one to explain, but i'll do my best -
I have an SSIS package which contains an Execute Process Task, this task executes a Powershell script. The script converts a .csv file to an Excel file and moves it to another folder for the next step to ingest the data into a table. Everything seems to run okay from within BIDS, however whenever I schedule this in a SQL agent job the task containing the Powershell script does not execute. I don't get an error on the Powershell task, it actually errors on the NEXT step which is an Excel task to ingest the file. The error I get says that the file doesn't exist, and when I check the folder where the file is supposed to be it is empty.
So my question:
How do I get SSIS to execute the Powershell script from the SQL agent job?
I know this is a permissions issue, and i've given the job account permissions to the Powershell script and the folder containing the files to convert. I'm assuming this should be simple (HA!), but not sure what else to try.
Any tips?
February 6, 2015 at 2:45 pm
Can you post a little more info? e.g. what is the executable you're calling (ie should be the path to powershell) and what do you have for the arguments (ie script name etc)
Steve.
February 6, 2015 at 3:36 pm
So I think this is an account issue with Powershell trying to access Excel via a launching user. While i'm not sure that I quite understand the mechanics of this, essentially I had to switch the user account in Component Services to Interactive User rather than Launching User (which is the default).
I followed this:
Under component services > Computers > My Computer > DCOM Config + Microsoft Excel application > properties > Identity Tab > Select the Interactive User > Ok.
Now, here's the thing - if Microsoft Excel Application doesn't show up in Component Services (which mine didn't), then try opening the comexp.msc file directly from C:\Windows\SysWOW64. If that doesn't work, then re-register the Excel.exe file with the server via command line - EXCEL.EXE –REGSERVER
I never would have considered that this would be an issue, definitely had to dig.
February 8, 2015 at 6:39 am
A little off topic, but it would be interesting to know why you would choose to convert from CSV to Excel prior to doing an import. I would usually suggest the exact opposite.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 8, 2015 at 6:08 pm
Polymorphist (2/6/2015)
So I think this is an account issue with Powershell trying to access Excel via a launching user. While i'm not sure that I quite understand the mechanics of this, essentially I had to switch the user account in Component Services to Interactive User rather than Launching User (which is the default).I followed this:
Under component services > Computers > My Computer > DCOM Config + Microsoft Excel application > properties > Identity Tab > Select the Interactive User > Ok.
Now, here's the thing - if Microsoft Excel Application doesn't show up in Component Services (which mine didn't), then try opening the comexp.msc file directly from C:\Windows\SysWOW64. If that doesn't work, then re-register the Excel.exe file with the server via command line - EXCEL.EXE –REGSERVER
I never would have considered that this would be an issue, definitely had to dig.
Within the PowerShell script there should be an object that is created to create an instance of Excel, this generally will have a setting that you state whether the Excel object is interactive or not. If you set this to invisible it will likely work without having to set all the component stuff.
However, I would question why you are not using native function of SSIS. SSIS is more than capable of going from CSV to Excel with much more control and will contain much more logging ability when troubleshooting issues.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
April 10, 2015 at 2:59 am
Polymorphist (2/6/2015)
So I think this is an account issue with Powershell trying to access Excel via a launching user. While i'm not sure that I quite understand the mechanics of this, essentially I had to switch the user account in Component Services to Interactive User rather than Launching User (which is the default).I followed this:
Under component services > Computers > My Computer > DCOM Config + Microsoft Excel application > properties > Identity Tab > Select the Interactive User > Ok.
Now, here's the thing - if Microsoft Excel Application doesn't show up in Component Services (which mine didn't), then try opening the comexp.msc file directly from C:\Windows\SysWOW64. If that doesn't work, then re-register the Excel.exe file with the server via command line - EXCEL.EXE –REGSERVER
I never would have considered that this would be an issue, definitely had to dig.
when i found yours solution it became so obvious 🙂
many thanks! it helped me
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply