DevOps, Continuous Delivery & Database Lifecycle Management
Automated Deployment

IN the previous article on automating your SQL Server best-practice reports, we saw how to export, as CSV files, all the information we’d gathered about a  SQL Server instance  that  related  how closely the server conformed to best-practices. Now I’ll show how we can easily automate the creation of an MS Word document. Of course there are many ways we can do this, and it is best to choose a way that suits your requirements, for example:

• COM Interop – automating an installation of MS Word to create a file.
• Open Xml SDK – automating the creation of a Word file via  .NET
• Open-Xml-PowerTools – Using Cmdlets that allow PowerShell to create, access or update MS Word files without requiring MS Word.

  I am not tempted to recommend a particular way: take what is good for you and move on. I’ll be using COM interop. It’s simpler to do, but to use this, you have to have a copy of Microsoft Word because you are using PowerShell to automate the application.

Previous articles on Simple-Talk have shown how you can use COM automation. This was always fine for short documents, but for documents of any size, it was painfully slow.  

Fortunately, the latest version of PowerShell in Windows Management Framework (WMF)  v5.0 solves this performance problem by making COM automation faster. The last published version is in Product Preview and you can download it here. I have been using it since CTP and have found it to be stable.  I strongly recommend that you install it in your desktop, but this application doesn’t require it.

The Solution

When I started to think about the best way of creating the MS Word document for this project, the same words come to my mind: KISS – L (Keep it simple Laerte). Other people may inherit your code and become responsible for maintaining it, and it would be unfortunate to have to require them to be an expert in PowerShell; so, I took some advice that I’ve learned on keeping scripts simple enough to make maintenance more simple (I am just pointing out some ones. There is a very good list in the Scripting Guys Site)

1. Do not use aliases
2. To what extent is it good to have everything in one command line? NOOOOOT at all
3. Do not use positional parameters. Always named parameters. Remember – other people will subsequently maintain your code.

…and the most important point:

Use reusable functions. All the checks that are done when the data is imported to an MS Word document need to be in a simple block of code. Just import the .CSV FILE and the functions will do the whole job

For a more extensive list of PowerShell Best Practices, check : Weekend Scripter: Best Practices for PowerShell Scripting in Shared Environment or search on Google for the phrase ‘PowerShell Best Practices‘.

How to Get Started

In designing this script, my intention was the same as with the collection of the data. The script must perform all the operations with one click. In this case, the operation is to create the word document and to put all the information we’ve gathered into it.

To get started:

1. Download the BestPracticesReview_Word.ps1 and BestPracticesReview_Word.bat from the head of the article
2. Copy these two files to the same directory. Copy all the CSV files from all instances from the directory you used to gather the information
3. Pay attention to the execution policy. Perhaps you need to change , using : Set-ExecutionPolicy RemoteSigned
   (more info on Using the Set-ExecutionPolicy Cmdlet)
4. Click  in the BestPracticesReview_Word.bat. It will create a word document for each instance copied to the folder.

How to Customize Your Report

The customization of this report is simple.

Below we have the word functions to work with the report.

Every check that is done in the script that collects the .CSV files needs to have a corresponding section added to the BestPracticesReview_Word.ps1 script to generate the MS Word file.  This might seem a bit clunky, but it means that you would find it much easier to customize each report, and requires as little understanding of PowerShell as possible. It just makes it a bit more awkward for the PowerShell superheros.

If you want to remove a best-practice check you just need to comment or remove the try-catch block in the both scripts. For instance the xp_msver section in the word script:

In the same way, if you want to add a check then you need to first collect the data and output to a CSV file as shown in the first script (first article): Then you’ll need to add the section in the MS Word script.

The Word Functions

Let’s take a look at the functions that you’ll want to understand if you are altering anything :

Start-MSWordDocumentSession

• This function starts the MS Word session. The parameters are Visible, (to show or not the MS Word document in real time), and Templatepath, (to use a MS Word template as document). It returns as ExitCode: either 0 and 1  and the error message

Remove-MSWordDocumentSession

• This function will end the MS Word session. The parameter ParamWordfileName gives the name of the file under which any changes need to be saved.

Add-MSWordText

• This function will add a Text in the Word Document. It has the parameters  text (the text to add) and its attributes  Color, FontName, Size, Bold and  Italic, if you have special formatting requirements

Add-MSWordParagraph

• This Function will add the text you supply as a parameter, using the built-in  Paragraph Style Style in the MS Word document. Use this function instead of the Add-MSWordText  to create sections in the word document. Also this function add the text to the TOC. The styles are listed in the WdBuiltinStyle enumeration

Add-MSWordTOC          

• This function will add the table of contents (ToC) at the beginning, based on the paragraph headings

Update-MSWordTOC

• This function will update the existing table of contents (ToC)

Add-MSWordTable

• This function will add the MS Word table, using the data within the PowerShell object called Object, and using, if possible the font-size Size. The beauty of this function is that you just need to load the .CSV file and pass the Object loaded to it. It will create the table and perform the ‘fontcolor’-based  thresholds if applicable, that were stored within the .CSV file (we saw the color algorithm in the previous article). These affect whether, for example, figures are highlit in red or amber  in order to catch the eye.

How It All Works

As in the Best Practices article, I tried to be as simple as I could. The design is based on the idea that the MS Word generation needs to be multi-instance because the solution to export the best-practice data is multi instance, too: A MS Word document for each one.

To accomplish that, we need a start point. In the CSV file export, I ´ve created a main .CSV file that will control the entire flow of the script. If this .CSV file is not there, none of the other ones will be processed and the document will not be generated. In addition, the loop will be performed in all of this specific CSV file.

You can be asking, ‘But Laerte, are you saying , for instance, that if I have 10 instances where I have created a set of CSVs for a report,  I can copy all of them into just one directory and run the .BAT file to call the PowerShell script to generate the MS Word, and that it will correctly create 10 MS Word documents? But how does the script know which CSV collection any individual CSV file is from? It is simple. In The export of .CSV files, I ´ve standardize the naming convention of the .CSV file with the InstanceName_NameoftheCheck. So the script will loop to the main .CSV file (XXXXXXXX_InstanceName.CSV) will split the .CSV filename at the _ char,  fill the variable $FullPath and then load the correct CSV, based on the InstanceName. The code is pretty simple:

Coding Flow

As I said before, after some checks, the script seeks for a main .CSV file and loop through the data in that. The next step is to start the MS Word session. If the function Start-MSWordDocumentSession returns true, the flow keep going to the next .CSV collection, otherwise it will keep in the loop.

So, it is time to add the Table of Contents (TOC). Yes, at this point of time there is no content yet, but we need to ADD the TOC and then in the end of the script UPDATE it.

The next Step is load the CSV files. I could think of something more generic, but that would require changing all my first idea. The script needs to be simple and require just one click

This way that I designed it, you need to specify in hard code what check you want to load.  At this point if you want to add a section, or a text it is time to do that .

First you just need to load the .CSV FILE and the ADD what you want. In the code, there is a dummy routine that you use for every extra check that you write

So if you’ve created a check called MYCHECK.CSV the code probably will be

To finish we just need to Update the TOC and then remove the MS Word session.

PS – if you want to see the magic in real time, change the line …

 … to …

Also if you want to pass a template document, just add the parameter -templatepath in this function

Conclusion

Now it is up to you to customize this and write your own checks. I’ve deliberately written clean and simple Classic PowerShell  without fancy tricks to encourage you to make this sort of report your own, perfectly customized for your particular requirements. Remember that you need to write code that can be easily changed by other people in your team, and your successors in your job. That’s why you need to keep things simple for this type of script..

DevOps, Continuous Delivery & Database Lifecycle Management
Go to the Simple Talk library to find more articles, or visit www.red-gate.com/solutions for more information on the benefits of extending DevOps practices to SQL Server databases.