geohernandez
Menu
  • HOME
  • ABOUT
  • CONTACT ME
  • WORK WITH GEO
    • Data Specialist
    • Speaker Events
    • Resume
  • English
    • English
    • Español
Menu

Deploying SQL Scripts with PowerShell

Posted on October 11, 2018October 11, 2018 by geohernandez

I always have considered the Continuous Delivery (CD) for Database world as one of the last frontiers to cross, it is not easy at all and the DevOps guys are brave witnesses about it, however, I consider that it is an aim which can be reachable with a mix of effort, compromise and obviously investment (time and money).

The previous aspects mentioned above are from my point of view the most valuable, but we also need to be realistic about in which stage of this process our companies are, I mean, it is not realistic or fair to pretend that a new startup consider spending many resources for implementing DB deployment under
CD vision, but if the team only requires to deploy through a few environments it can be feasible to use a simplistic approach based on using PowerShell.

In this article I will use Microsoft SQL Server as DB Engine and PowerShell, the main task will consist in getting a list of articles from Products table and export to CSV file with a tabular delimiter. I used to work with PowerShell ISE, but you can create and edit the PowerShell file even in Notepad, at the end the most important part is to be comfortable with the IDE chosen.

We should start turn on the cmdlet-style parameter, I would like to recommend this interesting link about this statement:

https://www.itprotoday.com/management-mobility/what-does-powershells-cmdletbinding-do

Basically, when we put on the top of our file we are enabled to start using common parameters and becoming our function in a new advanced function.  The next step consists in write the sentences for requesting a specific parameter which should match with a predefined list of environments, this parameter is mandatory and correspondence to position one, next statement is the validation of input parameter and is part of advance parameter which allows us to validate or constraint the value supplied by user, and finally it is assigned to $environmentDEPLOY variable.

# Parameters 
[CmdletBinding()] 
Param( [Parameter(Mandatory=$true,Position=1)] 
[ValidateSet('local', 'DEVELOP','INTEGRATION','LOAD','PRODUCTION')] 
[string] $environmentDEPLOY )

In short words, what did we make in the above code? Enable advanced functions and capture a new parameter which has been able to validate against a list or array of string, in case that validation passed it will assign the captured value to $environmentDEPLOY variable.  The next blocks will execute a group of if statement to assign the respective name of a server to a Server and DB variables.


if ($environmentDEPLOY -eq "local")

{

$Core_ServerName = "localhost"

$Core_DBName = "MyDB"

}

And for the rest of the environments you could have something like this:


if ($environmentDEPLOY -eq "DEVELOP")

{

$Core_ServerName = "NAME-DEVELOP-SERVER"

$Core_DBName = "MyDevelopDB"

}

Now, we have the logic to load Server and DB variables based on the user input.  For a simple illustration, we will be using the AdventureWorks DB and we are creating a query that I hope this part would be interesting for you, it should save as SQL script and contains the query, however, it could perfectly have a group of SQL sentences and call multiples queries or stored procedures, the big advantage, in this case, is the use of the Invoke-SQLCmd cmdlet, with him we can run sqlcmd script files in Powershell.

Check the next link for getting more details about the syntax of Invoke-SQLCmd:

https://docs.microsoft.com/es-es/sql/database-engine/invoke-sqlcmd-cmdlet?view=sql-server-2014

In our case, we only need to supply the values previously assigned to Server and DB variables, if you decide to specify an input file parameter only need to add a new parameter, in this article, I am using a predefined file which must be in the same folder than PowerShell script.


$result=Invoke-Sqlcmd -ServerInstance "$Core_ServerName" -InputFile 
"01_QueryToExecute.sql" -Database "$Core_DBName"

After executing the query, we have a request from a user to export the result into a CSV file.  You can see how many interesting cmdlets we have implemented until now, well, it is not all, we can use the cmdlet export-csv for taking the results got in the previous statement and export as CSV output file. The next statement will be the last inside the PowerShell script:

 
$result |export-csv MyOutputFile.csv -notypeinformation -delimiter "`t" 

Reassembling all, you should have a PowerShell script would look as this:

You can download the source code from my GitHub repository, here the link:

https://bit.ly/2OiVG11

Once time that we have completed the typing of our PowerShell script, we can run click on the play button in PowerShell ISE, it shows us the next image:

Maybe are you guessing if the validation is useful for this script, try to type any nonlogic name for the server and you should see a message like this:

Otherwise, if you supply a valid Environment input, and I refer to a value which is contained inside the list of ValidateSet statement it is going to work as expected.

Summary

As you have seen, the benefit of using this sort of mix between PowerShell and basic T-SQL for deploying through different Environment is clearly, easy and transparent.  Meanwhile, you work in more sophisticated solution close to a real CD approach, the approach covered in this article allow you to have a simple and easy to adapt deploy script.

Happy Querying!!!!

Category: Chronicles from the trenches, SSIS

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search for articles

Recent Posts

  • Quick Guide: BigQuery Service Account Setup Using gcloud
  • The Art of Data Modeling in AI times
  • Getting Started with Snowflake’s Snowpipe for Data Ingestion on Azure

Categories

  • Airflow (1)
  • Azure (6)
  • Azure DevOps (2)
  • Bash script (1)
  • Blog (1)
  • Cassandra (3)
  • Chronicles from the trenches (26)
  • Data Architecture (3)
  • Data Engineering (11)
  • DB optimization (2)
  • Events (2)
  • GIT (1)
  • MySQL (1)
  • Python (7)
  • Snowflake (3)
  • SQL Saturday (1)
  • SSIS (2)
  • T-SQL (5)
  • Uncategorized (2)

Archives

  • May 2025 (1)
  • March 2025 (1)
  • January 2025 (2)
  • October 2024 (1)
  • July 2024 (1)
  • May 2024 (1)
  • December 2023 (1)
  • November 2023 (1)
  • August 2023 (1)
  • June 2023 (1)
  • December 2022 (1)
  • November 2022 (1)
  • July 2022 (1)
  • March 2022 (1)
  • September 2021 (1)
  • May 2021 (1)
  • March 2021 (1)
  • February 2021 (3)
  • December 2020 (1)
  • October 2020 (3)
  • September 2020 (1)
  • August 2020 (1)
  • January 2020 (1)
  • August 2019 (1)
  • July 2019 (1)
  • June 2019 (1)
  • May 2019 (1)
  • April 2019 (1)
  • March 2019 (1)
  • November 2018 (3)
  • October 2018 (1)
  • September 2018 (1)
  • August 2018 (2)
© 2025 geohernandez | Powered by Minimalist Blog WordPress Theme