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 = 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.