Tag / PowerShell

by Arvind Shyamsundar

Using PowerShell to attach PST files to Outlook

This one is a really quick note. Yet another example to show off the power of the PowerShell pipeline and its interoperability with the COM world.

Recently I had to add 30+ existing PST files to an instance of Outlook. I hate using the mouse and keyboard for repetitive tasks (File –> Open Outlook Data File –> browse to the path –> click OK), so I set out to do the same through some form of scripting.

Luckily, PowerShell can create instances of COM objects, and Outlook provides a COM library to automate Outlook. It was a matter of a few minutes then to look around on Bing and compose the script:

Add-type -assembly "Microsoft.Office.Interop.Outlook" | out-null
$outlook = new-object -comobject outlook.application
$namespace = $outlook.GetNameSpace("MAPI")
dir “c:mypath*.pst” | % { $namespace.AddStore($_.FullName) }

One interesting thing with this is that you may need to run this with the correct version of PowerShell (32-bit is at c:windowssyswow64windowspowershellv1.0powershell.exe) to match the bitness of Outlook installed on your machine.

Also, if you do plan to use the above script, make sure you replace the path with the correct ones. Also do note that if the PST file path is incorrectly specified, Outlook will create a PST at that location, so be careful!

The AddStore method is documented here. Do note though this COM interface does not permit the addition of password protected PST files. In that case you may want to explore the third-party Redemption library.

Disclaimer

Note: Please note third-party links are provided as-is and Microsoft does not offer any guarantees or warranties regarding the content on the third party site.

This Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment.  THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.  We grant You a nonexclusive, royalty-free right to use and modify the Sample Code and to reproduce and distribute the object code form of the Sample Code, provided that You agree: (i) to not use Our name, logo, or trademarks to market Your software product in which the Sample Code is embedded; (ii) to include a valid copyright notice on Your software product in which the Sample Code is embedded; and (iii) to indemnify, hold harmless, and defend Us and Our suppliers from and against any claims or lawsuits, including attorneys’ fees, that arise or result from the use or distribution of the Sample Code.

This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm.

Advertisement
by Arvind Shyamsundar

PowerShell script to extract T-SQL task code from SSIS packages

Requirement

Some time back I presented a PowerShell script which extracts T-SQL code from RDL files. Remember that I created this script to assist me in doing code reviews of ad-hoc SQL snippets which were embedded in the report definition files.

Another common usage of ad-hoc T-SQL code is within SSIS packages, specifically the Execute SQL task or the (somewhat less commonly used) Execute T-SQL Statement Task. The sample script I provide in this blog post relates to the Execute SQL task.

Methodology

To do this efficiently we have a couple of choices:

  • We could parse the DTSX file (as it is finally a XML file) directly
  • We could use the managed API to load the package and iterate through the tasks

Given that our requirement is quite specific and we may want to reduce any dependency on managed objects, I chose to use the XML parsing approach. It does help very much that the DTSX package schema is fully documented (and I have kept in mind the changes done to the DTSX schema in SQL 2012.)

Scope: this script will focus on the Execute SQL task for the moment. Interested readers can modify the script to operate on the (rarely used) Execute T-SQL Statement Task. It will also skip any StoredProcedure invocation calls.

Usage

The TSQLfromDTSX.ps1 script takes 2 parameters: the path to the files and an option to recursively look at sub-folders.

powershell.exe .TSQLfromDTSX.ps1 -RootFolder <folder path> -Recurse <0 or 1>

Important: if the folder path has embedded spaces, you need to surround the path with single quotes (‘) and NOT the usual double quotes as you would expect.

Output

It outputs the extracted T-SQL code to the standard output, so you can simply redirect that to another file if required. An interesting feature I’ve plugged into the script is to prefix the T-SQL code with the name of the package and the specific task where that code was found. These ‘identity’ values are surrounded by the ~~ markers, which can make it easy for you to ‘tag’ these batches with some names in other activities.

This Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment.  THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED “AS IS” WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.  We grant You a nonexclusive, royalty-free right to use and modify the Sample Code and to reproduce and distribute the object code form of the Sample Code, provided that You agree: (i) to not use Our name, logo, or trademarks to market Your software product in which the Sample Code is embedded; (ii) to include a valid copyright notice on Your software product in which the Sample Code is embedded; and (iii) to indemnify, hold harmless, and defend Us and Our suppliers from and against any claims or lawsuits, including attorneys’ fees, that arise or result from the use or distribution of the Sample Code. 

This posting is provided “AS IS” with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm.

by Arvind Shyamsundar

PowerShell script to extract T-SQL code from RDL files

One of the things I do in my day job at Microsoft Services is to review database design and T-SQL code. Now, when it comes to code, there is a challenge just to find ALL the possible code which hits our database engine. This is because a non-trivial amount of T-SQL code is not in the form of Stored Procedures, but it is embedded inside other places – such as Reporting Services (SSRS).

Now if you have hundreds of RDL files, extracting T-SQL from those files is a very tedious job. Opening each file in Report Designer, you would have to comb through each dataset – a tedious and error prone procedure. This is where the knowledge of the RDL format is helpful. Luckily for us, the specification for RDL is documented and available publicly. Using this knowledge, I constructed a quick (and perhaps dirty Smile) PowerShell script to scan through multiple RDL files and extract the T-SQL command text.

To use the script just invoke it from PowerShell command prompt and pass in the root folder path (-RootFolder) where your RDL files are stored. The –Recurse parameter is also useful in case you have many subfolders where RDL files are stored.

Do leave comments on whether this script was useful for you!

This Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment.  THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.  We grant You a nonexclusive, royalty-free right to use and modify the Sample Code and to reproduce and distribute the object code form of the Sample Code, provided that You agree: (i) to not use Our name, logo, or trademarks to market Your software product in which the Sample Code is embedded; (ii) to include a valid copyright notice on Your software product in which the Sample Code is embedded; and (iii) to indemnify, hold harmless, and defend Us and Our suppliers from and against any claims or lawsuits, including attorneys’ fees, that arise or result from the use or distribution of the Sample Code.

This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm.

by Arvind Shyamsundar

T-SQL formatter: a PowerShell script

EDIT 19 Aug 2022: This post has not been updated for a long time, and the link to the sample code is broken. So, I recommend you take a look at Mala’s more recent blog post and associated script at https://www.sqlservercentral.com/articles/formatting-t-sql-scripts-using-scriptdom-and-powershell If you really want to look at my original sample code, a member of the community updated my older script and shared it as a gist: https://gist.github.com/jheidt/5320803

This is a ‘quick-and-not-so-dirty’ version of a T-SQL formatter / ‘pretty printer’ in PowerShell. To use this, please install the SQL 2012 SP1 version of the SQLDOM from the Feature Pack page. (If you already have SQL 2012 client tools on your machine, you should be ready to go.) And you will need PowerShell of course Smile

Here is a sample usage of this script:

tsqlpp.ps1 -Source c:temptestcases.sql -Target c:tempout.sql

Some usage tips:

  • Right click and save the file to your machine, do NOT run it directly from this blog site
  • To execute the script your PowerShell execution policy must be set to RemoteSigned
  • The parser is by default set to 110 compatibility i.e. SQL 2012 syntax. If you are working with older compatibility levels feel free to edit the script to reference the 80, 90, 100 versions of the parser.
  • The generated / formatted script will NOT contain comments, you will lose any comments you had in the unformatted version

Please leave your comments, suggestions and asks for future versions of the script in the Comments section of this post. Thank you and I’m glad to be of help to you!

Disclaimer: This script is provided AS IS with no warranties, and confer no rights.