I have designed a SSIS project and deployed it to SQL server and also created the job to run on daily basis but its giving me this error when executing this as job (doesnt give any error within VS):

There is this CLSid in this error message but there is no application associated to it in --> Component Services -> Computers -> My Computer -> DCOM Config

But this CLSid is registered inside registry editor

About this particular task on which this error is occurring: This is a script task which is modifying and deleting the un-wanted rows from the excel file in which I am trying to write SQL table data.

Script task code looks like this:

I have been working for hours now trying to fix this problem but no success. Kindly guide me how can I fix this issue. If any other information is required related to this project, please let me know....

CodePudding user response：

tl;dr;

You need to install Office (Excel) on the server AND ensure that you install it in a manner that mirrors the SQL Agent's expected bit-edness. Default for Agent is going to be 64bit, default for Office is still 32 :(

Error guessing

You have a script task that uses the Office interop libraries to delete some rows (2 through 11?) out of a spreadsheet.

You have Office installed on your machine and therefore you have the libraries installed. Excel still has COM based "stuff" in it, thus the interop and errors shrieking about the CLSid, registry, etc but that's likely just secondary errors because there is no base "application is not installed" exception to be thrown.

If Office is installed, then ensure your agent execution model matches the version of Office. If 32 bit Excel is already installed, don't potentially break everyone else's stuff by uninstalling and reinstalling as 64 bit, just got the Advanced section of the SQL Agent Job Step and check the 32bit box.

Once all that's done, then if you're still getting errors but new ones, then the existing comments mentioning permissions may come into play - it depends on where the Excel document actually exists (on the computer where SQL agent can access vs on the computer where it cannot vs networked drive)

Good luck in not finding people on the sanctions lists.

CodePudding user response：

Doing Excel automation in a SQL Server agent job is totally unsupported and probably won't work.

To have even a ghost of a chance of making this work you'll need to run a real desktop session on the server and automate Excel in that. Excel expects a real user to be logged in with a full profile. And Excel has failure conditions where it displays a popup window, which you'll need to be able to access via remote desktop.

You can read and write Excel files on a server with the OpenXML SDK, without actually having to run Excel. There's also a wrapper library called ClosedXML which you may find easier to use than using OpenXML directly.