SSIS runs well independently but fails in a Job, probably because of a Script Task

Question

SSIS runs well independently but fails in a Job, probably because of a Script Task

idogal

Old Hand

Points: 368
More actions
September 3, 2012 at 7:51 am

#261029

Hello,
I'm helping here with a SSIS package. The package loads data, exports it into Excel file, protects the Excel files with password through VB.NET Script Task and sends the file via Email.
When I'm running the package, either in BIDS or through the server after deployment, the package runs just fine. The problem is that it fails while it is executed through a job.
I tried several things and it seems that when I disable the VB.NET script task that protects the Excel with pass the job does run fine (and sends email messages).
I would appreciate any help with solving this situation. Thanks!
I had an email msg sent to me on pacakge OnError with ErrorDescription. I'm including the begging of the msg:
[font="Courier New"]System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Runtime.InteropServices.COMException (0x800A03EC): Exception from HRESULT: 0x800A03EC
at Microsoft.Office.Interop.Excel.Workbooks.Open(String Filename, Object UpdateLinks, Object ReadOnly, Object Format, Object Password, Object WriteResPassword, Object IgnoreReadOnlyRecommended, Object Origin, Object Delimiter, Object Editable, Object Notify, Object Converter, Object AddToMru, Object Local, Object CorruptLoad)
at ST_867abb47b0b24db1be33e436641258a9.vbproj.ScriptMain.Main()
--- End of inner exception stack trace ---[/font]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

David McKinney SSChampion Points: 10472 More actions · Answer 1

I reckon it's to do with the fact that when you schedule with a job, it runs under the sql agent service account. This means different permissions and a different context.

Could it be that the vb task doesn't find the file, because in the context of the sql agent account it is not where it is looking?

Or is it permission related?

Hope this gives you somewhere to start.

David McKinney SSChampion Points: 10472 More actions · Answer 2

you also may wish to google SSIS 0x800A03EC

There are issues around excel automation with interop running on a server.

good luck.

Rooster Mr or Mrs. 500 Points: 523 More actions · Answer 3

Is the operating system 64bit? If so try selecting "Use 32 bit runtime" from the Execution options tab in the SQL Server Agent job. This is found by editing the job step (where you select the SSIS Package to run).

It may also be a permission problem so you might need to create some credentials and a proxy account to execute the job step under. As a default the job is executed using the SQL Server Agent Service Account which (depending on the credentials used) may not have permissions to create a file in the directory.

idogal Old Hand Points: 368 More actions · Answer 4

Hi,

I was using a proxy account and I had already selected "Use 32 bit runtime". Thank you.

I was "playing" with all of the setting and with the VB.NET code and somehow the problem had been solved. Unfortunately, I couldn't trace what exactly had fixed it.

David, thanks for the advice, it was indeed wiser to google this error code.

Thank you all 🙂

Emiliano8 Newbie Points: 1 More actions · Answer 5

Emiliano8

Newbie

Points: 1

July 13, 2020 at 5:46 pm

#3769484

Hello

Can you solve this problem?

Thanks.