Set up job to convert word document to pdf with script task
-
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 doesn't convert the file.
Package is created on the server with SQL service agent account that has access to the word document, package and office word application. I previously tried from one approach by using bat file triggering exe. Now I'm trying another way to use c# in script task. It is clearer and easier to debug in my opinion. My previous post is here https://www.sqlservercentral.com/Forums/1903690/Set-up-job-to-convert-word-document-to-pdf?Update=1#bm1904039
The package looks like below. It converts the file first and insert exception message to a table.
The c# code is as below. I added some comments and try to catch exception and store in a variable.
#region Help: Introduction to the script task
#endregion#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using Microsoft.Office.Interop.Word;
using Microsoft.VisualBasic;
using Microsoft.VisualBasic.CompilerServices;#endregion
namespace ST_93d02cfa117444eb8a420c3d2172f4ce
{[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
static ScriptMain()
{
AppDomain.CurrentDomain.AssemblyResolve += new ResolveEventHandler(CurrentDomain_AssemblyResolve);
AppDomain.CurrentDomain.AssemblyResolve += new ResolveEventHandler(CurrentDomain_AssemblyResolve2);
}//Provide path to dll stored in folder on file system
static System.Reflection.Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)
{string path = @"E:\WordToPDF\references\";
return System.Reflection.Assembly.LoadFile(System.IO.Path.Combine(path, @"Microsoft.Office.Interop.Word.dll"));
//when debug add references local folder
}static System.Reflection.Assembly CurrentDomain_AssemblyResolve2(object sender, ResolveEventArgs args)
{string path = @"E:\WordToPDF\references\";
return System.Reflection.Assembly.LoadFile(System.IO.Path.Combine(path, @"Microsoft.VisualBasic.dll"));
//when debug add references local folder
}
public void Main()
{
try
{
// TODO: Add your code here
string docName = @"E:\WordToPDF\test\test\test.doc";
if (Operators.LikeString(docName, "*.doc", CompareMethod.Text) == false)
return;
if (File.Exists(docName) == false || File.Exists(docName.Replace(".doc", ".pdf")))
return;
var type = System.Type.Missing;
//new appication and open docuemnt
Microsoft.Office.Interop.Word.Application TheWordApp = new Microsoft.Office.Interop.Word.Application();
var TheDocument = TheWordApp.Documents.Open(docName);//unlink header and footer references
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();//convert to pdf
Thedocument.ExportAsFixedFormat(
docName.Replace(".doc", ".pdf"),
Microsoft.Office.Interop.Word.WdExportFormat.wdExportFormatPDF, false,
Microsoft.Office.Interop.Word.WdExportOptimizeFor.wdExportOptimizeForOnScreen,
Microsoft.Office.Interop.Word.WdExportRange.wdExportAllDocument, 1, 1,
Microsoft.Office.Interop.Word.WdExportItem.wdExportDocumentWithMarkup,
false, true,
Microsoft.Office.Interop.Word.WdExportCreateBookmarks.wdExportCreateNoBookmarks,
false, true, false, type);
//save close and remove original file
Thedocument.Save();
((Microsoft.Office.Interop.Word._Document)TheDocument).Close(ref type, ref type, ref type);
TheWordApp.Quit();
File.Delete(docName);
}
catch(Exception e)
{
Dts.Variables["User::ErrorMessage"].Value = "Message:"+e.ToString();
}
Dts.TaskResult = (int)ScriptResults.Success;
}#region ScriptResults declaration
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion}
}When I execute the package, it converts the file. When I run the job with only this package as one step, I got the exception as below.
Message:System.NullReferenceException: Object reference not set to an instance of an object. at ST_93d02cfa117444eb8a420c3d2172f4ce.ScriptMain.Main()
Please help provide some idea. Thank you very much.Kevin
-
Duplicate of https://www.sqlservercentral.com/Forums/1903690/Set-up-job-to-convert-word-document-to-pdf, where discussion already taken place.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - kding - Monday, October 23, 2017 10:10 PMI'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 doesn't convert the file.
Package is created on the server with SQL service agent account that has access to the word document, package and office word application. I previously tried from one approach by using bat file triggering exe. Now I'm trying another way to use c# in script task. It is clearer and easier to debug in my opinion. My previous post is here https://www.sqlservercentral.com/Forums/1903690/Set-up-job-to-convert-word-document-to-pdf?Update=1#bm1904039
The package looks like below. It converts the file first and insert exception message to a table.
The c# code is as below. I added some comments and try to catch exception and store in a variable.
#region Help: Introduction to the script task
#endregion#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using Microsoft.Office.Interop.Word;
using Microsoft.VisualBasic;
using Microsoft.VisualBasic.CompilerServices;#endregion
namespace ST_93d02cfa117444eb8a420c3d2172f4ce
{[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
static ScriptMain()
{
AppDomain.CurrentDomain.AssemblyResolve += new ResolveEventHandler(CurrentDomain_AssemblyResolve);
AppDomain.CurrentDomain.AssemblyResolve += new ResolveEventHandler(CurrentDomain_AssemblyResolve2);
}//Provide path to dll stored in folder on file system
static System.Reflection.Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)
{string path = @"E:\WordToPDF\references\";
return System.Reflection.Assembly.LoadFile(System.IO.Path.Combine(path, @"Microsoft.Office.Interop.Word.dll"));
//when debug add references local folder
}static System.Reflection.Assembly CurrentDomain_AssemblyResolve2(object sender, ResolveEventArgs args)
{string path = @"E:\WordToPDF\references\";
return System.Reflection.Assembly.LoadFile(System.IO.Path.Combine(path, @"Microsoft.VisualBasic.dll"));
//when debug add references local folder
}
public void Main()
{
try
{
// TODO: Add your code here
string docName = @"E:\WordToPDF\test\test\test.doc";
if (Operators.LikeString(docName, "*.doc", CompareMethod.Text) == false)
return;
if (File.Exists(docName) == false || File.Exists(docName.Replace(".doc", ".pdf")))
return;
var type = System.Type.Missing;
//new appication and open docuemnt
Microsoft.Office.Interop.Word.Application TheWordApp = new Microsoft.Office.Interop.Word.Application();
var TheDocument = TheWordApp.Documents.Open(docName);//unlink header and footer references
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();//convert to pdf
Thedocument.ExportAsFixedFormat(
docName.Replace(".doc", ".pdf"),
Microsoft.Office.Interop.Word.WdExportFormat.wdExportFormatPDF, false,
Microsoft.Office.Interop.Word.WdExportOptimizeFor.wdExportOptimizeForOnScreen,
Microsoft.Office.Interop.Word.WdExportRange.wdExportAllDocument, 1, 1,
Microsoft.Office.Interop.Word.WdExportItem.wdExportDocumentWithMarkup,
false, true,
Microsoft.Office.Interop.Word.WdExportCreateBookmarks.wdExportCreateNoBookmarks,
false, true, false, type);
//save close and remove original file
Thedocument.Save();
((Microsoft.Office.Interop.Word._Document)TheDocument).Close(ref type, ref type, ref type);
TheWordApp.Quit();
File.Delete(docName);
}
catch(Exception e)
{
Dts.Variables["User::ErrorMessage"].Value = "Message:"+e.ToString();
}
Dts.TaskResult = (int)ScriptResults.Success;
}#region ScriptResults declaration
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion}
}When I execute the package, it converts the file. When I run the job with only this package as one step, I got the exception as below.
Message:System.NullReferenceException: Object reference not set to an instance of an object. at ST_93d02cfa117444eb8a420c3d2172f4ce.ScriptMain.Main()
Please help provide some idea. Thank you very much.Kevin
You're still using drive letters instead of UNC paths.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply