Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1627
  • Last Modified:

SSIS (SQL server agent job) Script Task Error - Cannot create ActiveX component

I am running an SSIS package through a SQL server agent job and get this error:
"Source: <my excel macro script task>  Description: The script threw an exception: Cannot create ActiveX component."

When I run this package manually through the solution it runs properly, it's when the SQL server agent job tries to run it that it generates this error.

This package contains a simple script task that calls 2 excel macros in order to modify the source file before importing. This is the only way that I could think of to import a file with dynamic columns (running the macros to modify the column headers and other formatting functions).

Here's my script task sub routine code:

Dim xlapp As Object, wb As Object

xlapp = CreateObject("Excel.Application")
xlapp.visible = True

wb = xlapp.Workbooks.Open(<source file>.xls)

wb.application.Run(<macro1>)
wb.application.Run(<macro2>)

wb.Save()
wb.Close()
xlapp.Quit()

wb = Nothing

Simple, right? I just don't know where I'm going wrong. Any ideas?
As stated above, the error in the SS agent job is pointing to this particular script task.

Thanks,
Katie
0
DixieDev
Asked:
DixieDev
1 Solution
 
DixieDevAuthor Commented:
Excel isn't installed on the server where this package is running through the SS agent.
Oops! Problem solved. :o)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now