Set up job to convert word document to pdf
-
I'm working on a SQL job to transfer some data along with some word documents. After copying the word documents I need a step to convert them to pdf. The package I have runs fine but when I set it as a step in SQL job, it gives error.
I set up an execute process task to trigger a bat file. The bat file supplies file path as argument to an exe file.
The bat file is as below
E:\WordToPDF\test\Word2PDF.exe E:\WordToPDF\test\test\test.doc
The exe I created with c# using Microsoft Office Word library. Code is as below.
using System;
using System.IO;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop;
using Microsoft.Office.Interop.Word;
using System.Text.RegularExpressions;
using Microsoft.VisualBasic;
using Microsoft.VisualBasic.CompilerServices;class Sample
{
public static void Main(string[] args)
{
if (args.Length == 0)
return;
string docName = args[0].ToString();
if (Operators.LikeString(docName, "*.doc", CompareMethod.Text) == false)
return;
if (File.Exists(docName) == false || File.Exists(docName.Replace(".doc", ".pdf")))
return;
Microsoft.Office.Interop.Word.Application TheWordApp = new Microsoft.Office.Interop.Word.Application();
var TheDocument = TheWordApp.Documents.Open(docName);//foreach (Microsoft.Office.Interop.Word.Section wordSection in Thedocument.Sections)
//{
// Microsoft.Office.Interop.Word.Range footerRange = wordSection.Footers[Microsoft.Office.Interop.Word.WdHeaderFooterIndex.wdHeaderFooterPrimary].Range;
// footerRange.Font.ColorIndex = Microsoft.Office.Interop.Word.WdColorIndex.wdDarkRed;
// footerRange.Font.Size = 20;
// footerRange.Text = "Confidential";
//}
foreach (Section section in Thedocument.Sections)
{HeadersFooters headers = section.Headers; //Get all headers
foreach (HeaderFooter header in headers)
{
Fields fields = header.Range.Fields;
foreach (Field field in fields)
{
field.Unlink(); // update all fields in headers
}
}HeadersFooters footers = section.Footers; //Get all footers
foreach (HeaderFooter footer in footers)
{
Fields fields = footer.Range.Fields;
foreach (Field field in fields)
{if (Operators.LikeString(field.Code.Text.ToString(), "*PAGE*", CompareMethod.Text))
{
}
else
field.Unlink(); //update all fields in footers
}
}
}foreach (Field f in Thedocument.Fields)
{
f.Select();
f.Unlink();
}
Thedocument.Save();Thedocument.ExportAsFixedFormat(
docName.Replace(".doc", ".pdf"),
Microsoft.Office.Interop.Word.WdExportFormat.wdExportFormatPDF,
OptimizeFor: Microsoft.Office.Interop.Word.WdExportOptimizeFor.wdExportOptimizeForOnScreen,
BitmapMissingFonts: true, DocStructureTags: false);
Thedocument.Save();
((Microsoft.Office.Interop.Word._Document)TheDocument).Close();TheWordApp.Quit(false);
File.Delete(docName);}
}The error I got when I set it as job is as below
Executed as user: HPN\svracct. Microsoft (R) SQL Server Execute Package Utility Version 12.0.5000.0 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 11:19:57 PM Error: 2017-10-20 23:19:59.87 Code: 0xC0029151 Source: Execute Process Task Execute Process Task Description: In Executing "E:\WordToPDF\test\Test_convertword2PDF.bat" "" at "E:\WordToPDF\test\", The process exit code was "255" while the expected was "0". End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:19:57 PM Finished: 11:19:59 PM Elapsed: 2.028 seconds. The package execution failed. The step failed.Please help give some suggestion. Much appreciated!
Kevin
-
Is the task actually working though (did it create the PDF? The reason for SSIS reporting the error is as it says "The process exit code was "255" while the expected was "0"." If it is working, and producing the PDF, then you need to change the success value of your task. I doubt this is the answer, though.
If the application you've got is a custom application, I'd set up some logging on that instead. Get it to write out details of what it does to a log file and the errors it encounters. That'll give you a much better insight to where and what is happening, and why it's failing.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Quick thought, add error handling in the exe code with specific error numbers, then you at least will have a chance of knowing what is going wrong.
😎 - Thom A - Saturday, October 21, 2017 5:28 AM
Thank you for your reply. If I run the package, it converts the document to PDF. When I set the package as a step of the job. It doesn't convert the file. And give the error I posted. I tried to change exit code to 255. Job runs successfully, but the file is not converted.
Kevin
- Eirikur Eiriksson - Saturday, October 21, 2017 6:28 AM
Thank you for your reply. I will try to add error handling to the c# code. I think it may be some access issue so the exe only runs half way. I will post my find out.
Kevin
- kding - Saturday, October 21, 2017 5:14 PM
When you run the package, you probably do so within Visual Studio, and you get your id as the execution context. When you run the package as a job step in a SQL Agent job, the id that is used for the SQL Server Agent service is used instead, and IT must have the necessary file permissions to access the executable file as well as the data file source and destination folder locations.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - sgmunson - Monday, October 23, 2017 8:23 AM
To follow up on this, the default account used for SQL Agent jobs is a SQL-only login, so it will not have rights to your file system.
John
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - drew.allen - Monday, October 23, 2017 9:32 AM
Excellent point !!!
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - drew.allen - Monday, October 23, 2017 9:32 AM
Thnk you John. I thought about this too. I was login as SQL server agent account when I run the package to avoid this access issue. The package runs fine but not the job.
Kevin
- kding - Monday, October 23, 2017 10:37 AM
You should also always use UNC paths instead of drive letters. The E:\ drive when you run it on your machine is not going to be the same as the E:\drive when it runs on the server.
Drew
PS: Sorry about signing the previous post John.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - drew.allen - Monday, October 23, 2017 3:15 PM
Yes. That's a good point too. I was actually logging in to the server as service agent account.
Kevin
-
Thank all of you for posting the reply. I found some pretty good ideas and things to test. I haven't figure out the solution yet. I found another approach for this, and I will use scrip task to directly run c# in package. Even I still have the same issue(package runs fine but job failed) It is easier to debug and I'm able to catch some exception from there. I feel editing my original post may be confusing, I will close this topic and open another one,
Thank you very much!
Kevin
- kding - Monday, October 23, 2017 8:35 PM
I changed the drive letter path e:\ to UNC path \\127.0.0.1\e$\, it gives me the same error.
Kevin
-
The issue has been resolved with help from my coworker. We tried to catch the exception from c# code. exception is as below.
Message:System.Runtime.InteropServices.COMException (0x80080005): Retrieving the COM class factory for component with CLSID {000209FF-0000-0000-C000-000000000046} failed due to the following error: 80080005 Server execution failed (Exception from HRESULT: 0x80080005 (CO_E_SERVER_EXEC_FAILURE)). at System.Runtime.Remoting.RemotingServices.AllocateUninitializedObject(RuntimeType objectType) at System.Runtime.Remoting.Activation.ActivationServices.CreateInstance(RuntimeType serverType) at System.Runtime.Remoting.Activation.ActivationServices.IsCurrentContextOK(RuntimeType serverType, Object[] props, Boolean bNewObj) at System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean noCheck, Boolean& canBeCached, RuntimeMethodHandleInternal& ctor, Boolean& bNeedSecurityCheck) at System.RuntimeType.CreateInstanceSlow(Boolean publicOnly, Boolean skipCheckThis, Boolean fillCache, StackCrawlMark& stackMark) at System.RuntimeType.CreateInstanceDefaultCtor(Boolean publicOnly, Boolean skipCheckThis, Boolean fillCache, StackCrawlMark& stackMark) at System.Activator.CreateInstance(Type type, Boolean nonPublic) at System.Activator.CreateInstance(Type type) at ST_93d02cfa117444eb8a420c3d2172f4ce.ScriptMain.Main()
We found the solution for COM exception from link below https://stackoverflow.com/questions/12300046/couldnt-find-microsoft-word-document-in-dcom-config. The SQL service agent account cannot execute Office Word. After setting this up, the job runs perfectly.Thank you for your help.
Kevin
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply