Writing to word doc from SQL SERVER
Hi Guys, I have a template and i need to fill the data from SQL SERVER Database. please advise me how to get it done in SSIS/SQL SERVER T-SQL. Thanks a Lot
May 29th, 2012 5:36pm

1. You can create an SSIS package with an ADO. NET source connecting to SQL Server. 2. Searching on the web there're folks using Script Task that writes to the word. You need to add a reference to microsoft.office.interop.word (.NET) file in your script. This posting is provided "AS IS" with no warranties, and confers no rights.
Free Windows Admin Tool Kit Click here and download it now
May 29th, 2012 11:24pm

Hi, Here's my test for you.. Step 1. In SSIS package, I created three package variables - Name - Simon Category - Integration Services Question - Writing to word doc from SQL SERVER These three variables plays the role of data which are extracted from database, just a simulation. Step 2 Drag a script task to Control Flow, and pass above three variables as the value get from database. Note - If you really extract data from database, you may need a data flow and add a ADO.NET Source or OLE DB Source, then pass the data to a 'Script Component' in data flow, the script may be a little different to my currently posted scripts, but I just made a demo for you and let you know we can do it by C# script. Step 3 Click Edit Script, then the first thing you need to do is to add reference for Word scripting..DLL. Right Click 'References' and 'Add Reference', choose 'COM' and to find the component name.. Because every version in SSIS is different, but you just need to find Microsoft Word ** Object Libary. My current version is 14.0. So I can find Microsoft Word 14.0 Object Library. Add this reference. Then you will see there're some new added references in your project explorer - Microsoft.Office.Core Microsoft.Office.Interop.Word .. Step 4 Coding script I post to you, you can try to understand which values are passed and which words in document are replaced (You said you have a template in document). /* Microsoft SQL Server Integration Services Script Task Write scripts using Microsoft Visual C# 2008. The ScriptMain is the entry point class of the script. */ using System; using System.Data; using Microsoft.SqlServer.Dts.Runtime; using System.Windows.Forms; using Microsoft.Office.Interop.Word; using System.IO; namespace ST_6206e13776db4151ab892b9078c5cb42.csproj { [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")] public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase { #region VSTA generated code enum ScriptResults { Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success, Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure }; #endregion public void Main() { // TODO: Add your code here Dts.TaskResult = (int)ScriptResults.Success; object fileName = "d:\\TestWord.docx"; object saveAs = "d:\\NewTest.docx"; string name = Dts.Variables["User::Name"].Value.ToString(); string category = Dts.Variables["User::Category"].Value.ToString(); string question = Dts.Variables["User::Question"].Value.ToString(); CreateWordDocument(fileName,saveAs,name,category,question); } private void CreateWordDocument(object fileName,object saveAs,string name, string category, string question) { object missing = System.Reflection.Missing.Value; Microsoft.Office.Interop.Word.Application wordApp = new Microsoft.Office.Interop.Word.ApplicationClass(); Microsoft.Office.Interop.Word._Document aDoc = null; if (File.Exists((string)fileName)) { DateTime today = DateTime.Now; object readOnly = false; object isVisible = false; wordApp.Visible = false; aDoc = wordApp.Documents.Open(ref fileName, ref missing, ref readOnly, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref isVisible, ref missing, ref missing, ref missing, ref missing); aDoc.Activate(); this.FindAndReplace(wordApp, "<name>", name); this.FindAndReplace(wordApp, "<category>",category); this.FindAndReplace(wordApp, "<question>", question); //aDoc.Content.InsertBefore("This is at the beginning\\"); //aDoc.Content.InsertAfter("This is at the end\\"); } else { MessageBox.Show("File does not exist"); return; } aDoc.SaveAs(ref saveAs, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing); aDoc.Close(ref missing, ref missing, ref missing); //MessageBox.Show("File Created"); } private void FindAndReplace(Microsoft.Office.Interop.Word.Application WordApp, object findText, object replaceWithText) { object matchCase = true; object matchWholeWord = true; object matchWildCards = false; object matchSoundsLike = false; object nmatchAllWordForms = false; object forward = true; object format = false; object matchKashida = false; object matchDiacritics = false; object matchAlefHamza = false; object matchControl = false; object read_only = false; object visible = true; object replace = 2; object wrap = Microsoft.Office.Interop.Word.WdFindWrap.wdFindContinue; object replaceAll = Microsoft.Office.Interop.Word.WdReplace.wdReplaceAll; WordApp.Selection.Range.HighlightColorIndex = WdColorIndex.wdDarkRed; Range rng = WordApp.Selection.Range; rng.Font.Color = WdColor.wdColorBlue; WordApp.Selection.Find.Execute( ref findText, ref matchCase, ref matchWholeWord, ref matchWildCards, ref matchSoundsLike, ref nmatchAllWordForms, ref forward, ref wrap, ref format, ref replaceWithText, ref replaceAll, ref matchKashida, ref matchDiacritics, ref matchAlefHamza, ref matchControl); } } } After this coding, then you can try to execute your package, what I got from this test please refer to below figure. I guess your document template is more complicated than this one, but you can try this demo and get some tips from it. Hope it helps! Vote if it's helpful! Simon MSN:simonlv@live.cn Skype:simonlvpin
May 30th, 2012 5:27am

Hi, Here's my test for you.. Step 1. In SSIS package, I created three package variables - Name - Simon Category - Integration Services Question - Writing to word doc from SQL SERVER These three variables plays the role of data which are extracted from database, just a simulation. Step 2 Drag a script task to Control Flow, and pass above three variables as the value get from database. Note - If you really extract data from database, you may need a data flow and add a ADO.NET Source or OLE DB Source, then pass the data to a 'Script Component' in data flow, the script may be a little different to my currently posted scripts, but I just made a demo for you and let you know we can do it by C# script. Step 3 Click Edit Script, then the first thing you need to do is to add reference for Word scripting..DLL. Right Click 'References' and 'Add Reference', choose 'COM' and to find the component name.. Because every version in SSIS is different, but you just need to find Microsoft Word ** Object Libary. My current version is 14.0. So I can find Microsoft Word 14.0 Object Library. Add this reference. Then you will see there're some new added references in your project explorer - Microsoft.Office.Core Microsoft.Office.Interop.Word .. Step 4 Coding script I post to you, you can try to understand which values are passed and which words in document are replaced (You said you have a template in document). /* Microsoft SQL Server Integration Services Script Task Write scripts using Microsoft Visual C# 2008. The ScriptMain is the entry point class of the script. */ using System; using System.Data; using Microsoft.SqlServer.Dts.Runtime; using System.Windows.Forms; using Microsoft.Office.Interop.Word; using System.IO; namespace ST_6206e13776db4151ab892b9078c5cb42.csproj { [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")] public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase { #region VSTA generated code enum ScriptResults { Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success, Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure }; #endregion public void Main() { // TODO: Add your code here Dts.TaskResult = (int)ScriptResults.Success; object fileName = "d:\\TestWord.docx"; object saveAs = "d:\\NewTest.docx"; string name = Dts.Variables["User::Name"].Value.ToString(); string category = Dts.Variables["User::Category"].Value.ToString(); string question = Dts.Variables["User::Question"].Value.ToString(); CreateWordDocument(fileName,saveAs,name,category,question); } private void CreateWordDocument(object fileName,object saveAs,string name, string category, string question) { object missing = System.Reflection.Missing.Value; Microsoft.Office.Interop.Word.Application wordApp = new Microsoft.Office.Interop.Word.ApplicationClass(); Microsoft.Office.Interop.Word._Document aDoc = null; if (File.Exists((string)fileName)) { DateTime today = DateTime.Now; object readOnly = false; object isVisible = false; wordApp.Visible = false; aDoc = wordApp.Documents.Open(ref fileName, ref missing, ref readOnly, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref isVisible, ref missing, ref missing, ref missing, ref missing); aDoc.Activate(); this.FindAndReplace(wordApp, "<name>", name); this.FindAndReplace(wordApp, "<category>",category); this.FindAndReplace(wordApp, "<question>", question); //aDoc.Content.InsertBefore("This is at the beginning\\"); //aDoc.Content.InsertAfter("This is at the end\\"); } else { MessageBox.Show("File does not exist"); return; } aDoc.SaveAs(ref saveAs, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing); aDoc.Close(ref missing, ref missing, ref missing); //MessageBox.Show("File Created"); } private void FindAndReplace(Microsoft.Office.Interop.Word.Application WordApp, object findText, object replaceWithText) { object matchCase = true; object matchWholeWord = true; object matchWildCards = false; object matchSoundsLike = false; object nmatchAllWordForms = false; object forward = true; object format = false; object matchKashida = false; object matchDiacritics = false; object matchAlefHamza = false; object matchControl = false; object read_only = false; object visible = true; object replace = 2; object wrap = Microsoft.Office.Interop.Word.WdFindWrap.wdFindContinue; object replaceAll = Microsoft.Office.Interop.Word.WdReplace.wdReplaceAll; WordApp.Selection.Range.HighlightColorIndex = WdColorIndex.wdDarkRed; Range rng = WordApp.Selection.Range; rng.Font.Color = WdColor.wdColorBlue; WordApp.Selection.Find.Execute( ref findText, ref matchCase, ref matchWholeWord, ref matchWildCards, ref matchSoundsLike, ref nmatchAllWordForms, ref forward, ref wrap, ref format, ref replaceWithText, ref replaceAll, ref matchKashida, ref matchDiacritics, ref matchAlefHamza, ref matchControl); } } } After this coding, then you can try to execute your package, what I got from this test please refer to below figure. I guess your document template is more complicated than this one, but you can try this demo and get some tips from it. Hope it helps! Vote if it's helpful! Simon MSN:simonlv@live.cn Skype:simonlvpin
Free Windows Admin Tool Kit Click here and download it now
May 30th, 2012 5:29am

Hi, Does this method help you? Or do you meet any new problem ?Hope it helps! Vote if it's helpful! Simon MSN:simonlv@live.cn Skype:simonlvpin
June 1st, 2012 4:30am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics