Post reply

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

  • 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]

  • September 3, 2012 at 8:36 am

    #1532994

    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.

  • September 3, 2012 at 8:46 am

    #1533002

    you also may wish to google SSIS 0x800A03EC

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

    good luck.

  • September 3, 2012 at 9:04 am

    #1533009

    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.

  • September 6, 2012 at 3:38 am

    #1534334

    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 🙂

  • July 13, 2020 at 5:46 pm

    #3769484

    Hello

    Can you solve this problem?

    Thanks.

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

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