Package executes manually but not with agent job on SQL Server

[kalvis]

Created and SSIS package in VS 2008. The package contains a script task with a reference to an assembly for PDFSharp. I ran a build on the script containing this assembly. After saving the package changes I ran a build on the project containing the package to create the manifest file for the creation of the installation package. The package was deployed to a 2008 64 bit SQL Server database using this installation package.

This package will execute successfully in my local environment through BIDS. It will also execute successfully by running the package manually in Management Studio by right clicking and clicking Run. But, no matter what I do I cannot get the package to execute through an Agent Job in Management Studio. The agent log reads as follows below. Any thoughts on this are appreciated. Thanks!

Message
Executed as user: xxxx\xxxxxxx. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 8:56:53 AM Error: 2015-01-07 08:56:54.06 Code: 0x00000001 Source: Create PDF Copy of Report Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.IO.FileNotFoundException: Could not load file or assembly 'PdfSharp, Version=1.3.1684.0, Culture=neutral, PublicKeyToken=xxxx' or one of its dependencies. The system cannot find the file specified. File name: 'PdfSharp, Version=1.3.1684.0, Culture=neutral, PublicKeyToken=xxxx' at ST_5ca022c8b8ab491fb941ed0d214dfbbc.vbproj.ScriptMain.Main() WRN: Assembly binding logging is turned OFF. To enable assembly bind failure logging, set the registry value [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1. Note: There is some performance penalty associated with assembly bind failure logging. To turn this feature off, remove the registry value [HKLM\Software\Microsoft\Fusion!EnableLog]. --- End of inner exception stack trace --- at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript() End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 8:56:53 AM Finished: 8:56:54 AM Elapsed: 0.281 seconds. The package execution failed. The step failed.

 

[fredericofonseca]

I assume you are executing this on your own machine sql server instance, not on a server.

did you install a 64 bit version of PDFSharp? If not you will need to use it unless you use the dtexec 32 bit


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[kalvis]

I initially executed the package locally from my own machine. But, after developing the package and exporting to SQL Server. I ran it directly from Management Studio on the server. I am going to go back and see which version of PDFSharp I used. Since I developed locally on my 32 bit machine I suspect it had to have been the 32 bit version.

But, one question I have if this potentially is the issue. Why would I be able to execute the package from Management Studio, on the server manually but not through an agent job. Wouldn't an issue with 32 versus 64 bit PDFSharp cause issues with both of these methods?

Thanks Frederico for your help!

 

[fredericofonseca]

you stated 2 ways you did run the package.
1 - visual studio - this runs in 32 bit mode
2 - right click and execute run - again this runs in 32 bit mode so if you did this in the server that means you installed the 32 bit version on the server also

3 - through a sql server agent job - this executes the package in 64 bit mode unless you specify otherwise.


so you either install the 64 bit version of pdfsharp or you execute the package in 32 bit mode by using the correct dtexec.
this is normally located in
C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe

give it a try by executing the package on sql server agent not as a "execute ssis package" but as a command line exec where you supply all required parms to your package if any and where you supply the full path to dtexec as per above (taking in consideration the drive/path where your install is)

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886