Can you run PowerShell in SQL Server Management Studio? YES!

Just to be clear!! You can run PowerShell in SQL Server Management Studio.

Not only you can run PowerShell, but you can create scheduled jobs in SQL Agent to run PowerShell scripts. This has been available since SQL Server 2008 (before R2). And, this is a lot better now, as each version are finally providing more “SQL PowerShell cmdlets” to manage your SQL Server in their *”SQLPS” PowerShell module.

*Note: SQLPS Module was introduce with SQL Server 2008.

Nowadays, Thanks to both Aaron Nelson, Christy LeMaire, and Rob Sewell who have contribute to the success of provide new enhancements to SQL Server PowerShell (SQLPS) cmdlets. Check out their tools:

DBA Tools “best practices and instance migration module” link: https://dbatools.io/
DBA Reports “free, fun” link: https://dbareports.io/

By the way, DBA Reports is owned by Rob Sewell – @sqldbawithabeard. Great Work!!

So, YES! You can run PowerShell from SQL Server Management Studio.

How to run PowerShell?

You can Right-Click on most of the SQL Server objects under “Object Explorer” and look for “Start PowerShell“.

This will open the PowerShell prompt and you are ready to start your adhoc scripting.

Keep in mind, on the latest version of SQL Server (< 2012), the SQL PowerShell module (SQLPS) is loaded and already available. This will create a SQL Server Drive connecting (in this case) to your local instance installation or whichever instance you’re connecting to.

Notice, in my case, the above image will open a PowerShell prompt and is using PowerShell version 5.1 which is part of my Windows 10. The same will be true on earlier OS version of PowerShell. The “Start PowerShell” will open the current PowerShell version installed on that machine.

Another thing to understand, although you already have a set of available cmdlets to manage your SQL Server, you can still expand and build more script with the use SMO (SQL Server Management Objects). So, the possibilities to build your own solutions are endless.

All SMO .NET assemblies are loaded into your system when installing SSMS.

About SQLPS been removed

To be clear! Documentation states that SQLPS “Utility” (sqlps.exe) will be removed in the future. But, the SQLPS PowerShell module will still be available. (See reference link)
https://technet.microsoft.com/en-us/library/cc280450(v=sql.130).aspx

This is why you rather use the normal PowerShell console and start using the SQLPS module. Keep in mind, that since PowerShell 3.0, all existing installed modules are automatically loaded and ready to use in your PowerShell session.

How do I get SQL PowerShell?

Simple! SQL PowerShell comes included when SQL Server Management Studio(SSMS) is installed. For sometime ago SSMS (SSMS 2012) has been available to install separately (free-of-charge). As a matter of fact, you could install three separate SQL Server Features components without the need of installing SSMS and start scripting against your SQL engine.

The following link shows both latest version of SSMS (16.5.3) and the preview SSMS for SQL Server vNext (RC 17) can be found here:
https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

Bonus – No need for SSMS GUI nor the SQL Engine

Sometimes there’s no need to install a SQL instance, nor SSMS GUI but only the necessary components installed in order to run and scheduled some SQL PowerShell scripts in Windows Server Task Scheduler. I had this scenario on a **server with no SQL engine but needed to run some scheduled SQL PowerShell scripts. Only 3 components are needed:

(Below content extracted from Microsoft link (Install section) : https://www.microsoft.com/en-us/download/details.aspx?id=52676 )

Microsoft® Windows PowerShell Extensions for Microsoft SQL Server® 2016
The Microsoft Windows PowerShell Extensions for SQL Server includes a provider and a set of cmdlets that enable administrators and developers to build PowerShell scripts for managing instances of SQL Server. The SQL Server PowerShell Provider delivers a simple mechanism for navigating SQL Server instances that is similar to file system paths. PowerShell scripts can then use the SQL Server Management Objects to administer the instances. The SQL Server cmdlets support operations such as executing Transact-SQL scripts or evaluating SQL Server policies.

Filename: X86 and x64 Package (PowerShellTools.msi)

– Microsoft® SQL Server® 2016 Shared Management Objects
SQL Server Management Objects (SMO) is a .NET Framework object model that enables software developers to create client-side applications to manage and administer SQL Server objects and services.

Note: Microsoft SQL Server Management Objects requires – Microsoft SQL Server System CLR Types, that is available on this page.
Filename: X86 and x64 Package (SharedManagementObjects.msi)

– Microsoft® System CLR Types for Microsoft SQL Server® 2016
The SQL Server System CLR Types package contains the components implementing the geometry, geography, and hierarchy id types in SQL Server. This component can be installed separately from the server to allow client applications to use these types outside of the server.

Filename: X86 and x64 Package (SQLSysClrTypes.msi)

**Note: This can apply to desktop/laptop is you don’t want to install the whole SQL Server CD. As long as, you have remote connection to a SQL Server system, then you just start building scripts. You will save some disk space too.

Florida PowerShell Upcoming activities for May and June 2016

It’s a busy and a good time to learn some PowerShell.

1. Where: Florida PowerShell User Group Monthly meeting, Date: Thursday, May 26th at 6:30pm. Online
Topic: The Essential PowerShell on Error Trapping.
Description:
Do you to learn how to trap and document error while running PowerShell scripts? This session will cover the use and how to trap errors in your PowerShell script. We’ll be creating simple script providing some scenarios in trapping errors. At the same time, we are going to end up creating an error report.

Register at Eventbrite: https://www.eventbrite.com/e/florida-powershell-user-group-monthly-meeting-may-2016-tickets-25454080841

2. Where: Idera Geek Synch, Date: Wednesday, June 1st at 12:00pm – 01:00pm, Online
Topic: The Essential PowerShell Tools for the DBA Administrator.
Description: I will covers the some of my favorite PowerShell tools I uses on a regular basis. In this session I will be showing some available tools the DBA can use along with PowerShell. I’ll be integrating Visual Studio with PowerShell and at the same time using IDERA’s PowerShellPlus editor. At the end, we’ll build an SSIS package solution to execute our PowerShell script. It will be pack with interesting thing to do as well as help you accomplish your automation tasks.

Register at: https://www.idera.com/events/geeksync

3. Where: IT Pro Camp Jacksonville. Date: Saturday, June 11th All Day Event. (In Person)
Topic: The Essentials of Tackling PowerShell Basic Functions
Description:  I will demonstrate creating a PowerShell function from a one-liner and/or an existing script file in its basic form. This is an example of the script evolution which you’ll experience while building you PowerShell skills.

Register at: http://itprocamp.com/

4. Where: SQLSaturday South Florida. Date: Saturday, June 18th, All Day Event. (In Person)
Topic: SSIS – Integrating PowerShell in a ScriptTask component
Description: This session will demostrate how you can reuse a PowerShell script in SSIS “Script Task” component as part on a ETL flow.  I’ll be showing some basic .NET Script code in both C# and VB.  I’ll be adding some useful tips when re-using existing Powershell code. Integrating different .NET technologies in a SSIS package: C#, VB.NET, XML, and PowerShell.

Register at: http://www.sqlsaturday.com/524/EventHome.aspx

Come and say Hi!

PowerShell in South SQL Saturday 379 was a Great Success

SQLSaturdaySoFlorida2015

Once again I’m thankful to the organizers to have me speak at this “Awesome” event.  I appreciate the all whom attend my session meking it a Great Success and they got more.  My “PowerShell with Visual Studio SQL Data Tools” session became also a “SMO Simplicity Recap” session. They got two session in one.

SQLSat379_02

 

Session highlights

1. Visual Studio Community 2013 is the environment to use for integrated development by including Microsoft and Third-Party tools like:
a. PowerShell Tools for Visual Studio
b. Python Tools for Visual Studio
c. SQL Data Tools – Business Intelligent Developement
d. PowerShell Studio 2015  – call from within Visual Studio
e. PrimalXML 2015 – Call from within Visual Studio
d. And many more can be added…

SQLSat379_03

2. Visual Studio integration with either Team Foundation and Github repositories.

3. A quick dive in XML objects.

4. PowerShell error trapping in integrated solution.

SQLSat379_06

5. PowerShell SMO embedding and executing T-SQL code.

SQLSat379_05

During my presentation I demo for the first time how Visual Studio can trap PowerShell errors from within a SSIS Script Task component. And, everyone dropped their mounth. Beside the fact that you can also run and trap script errors by executing by itself.

SQLSat379_04

In the SMO session, I show how useful the ScriptBlock can be when embedding and running T-SQL code while PowerShell reads one object at a time.

To download my presentation and demo files are all available under South Florida SQLSaturday Schedule page:
http://www.sqlsaturday.com/379/Sessions/Schedule.aspx

Once again, THANKS to everyone for your attendance and support.

IT Palooza South Florida Great Success – PowerShell Rules!

Great event on 12/12/2013 in South Florida with most IT Community User Groups showcasing their stuff. It was full of people and friends.

Florida PowerShell User Group represented!
Florida PowerShell User Group represented!
List of community IT User Groups
List of community IT User Groups
At Nova University, Davie FL.
At Nova Southeastern University, Davie FL.

Thanks to Nova Southeastern University and organizers (specially Alex Funkhouser) for having me as a speaker present a little bit of PowerShell.

Me with Microsoft IT Pro Evangelist Blain Barton.
Me with Microsoft IT Pro Evangelist Blain Barton.
Sharing table with Microsoft.
Sharing table with Microsoft.

Also, thanks to all who attended my session on “Integrating PowerShell in SSIS Script Task“. It was a great to pass some knowledge and see everyone participating.

Thank You!

PowerShell Community Tools loaded in SQL Data Tools BI.
PowerShell Community Tools loaded in SQL Data Tools BI.

Here’s the presentation (fully loaded):

This presentation contains LOTS of goodies in it.

1. It contains a .NET console solution.
2. SSIS solution (no-NONsense).
3. Sample PowerShell scripts
4. Sample .NET code both C# and VB.NET (code snippets)

All this material so you can study it. Take your time and make it your own.

This folder (C:\TempSSIS) need to be copied to the root of the C:\ drive.

Things to pay attention too. Here’s some other important updates for Visual Studio 2012:
1. Visual Studio 2012 Team Explorer 8/2/2012 – http://www.microsoft.com/en-us/download/details.aspx?id=30656
2. Visual Studio 2012 Update 4 11/12/2013 – http://www.microsoft.com/en-us/download/details.aspx?id=39305

Don’t forget to Team Foundation Online (free up to 5 users): http://www.visualstudio.com/products/visual-studio-online-overview-vs

Plus two PowerShell community tools you should consider using with Visual Studio:
1. “PowerShell Tools for Visual Studio” by Adam Driscoll (PowerShell MVP) – http://visualstudiogallery.msdn.microsoft.com/c9eb3ba8-0c59-4944-9a62-6eee37294597
2. “StudioShell” by Christopher Beefarino (PowerShell MVP) – http://studioshell.codeplex.com/

Keep learning PowerShell!

Nice DOTNETZIP Integration with PowerShell

Let me share a script I built two years ago, and I just created a function for it named “New-ZipFile“. Basically, this PowerShell function script will create a blank zipped file and copy the files to it. At the same time if you run it again (after updating an existing file) will overwrite the file any existing files previously on the existing zipfile. Also, there’s no prompt.

This is an example of what PowerShell can provide at an excellent tool for providing creative solutions. Also the community is very active is helping everyone.

I agree that sometime is not easy but definitely not impossible. And there’s lots of other possible good alternative. But, using PowerShell let you customized your solution with an opportunity for enhancements giving you some level of control over what you want to accomplish.

I’m using the DOTNETZIP from Codeplex for this example. By the way, they provide good documentation on how to use the API’s. (hint: copy all the “Tools” folder to “Program Files (x86)\DotNetZip\..” folder)

You can download DOTNETZIP at the following link: http://dotnetzip.codeplex.com/

Here’s the sample script. Just change the variables values to your need, and make it your own:

## - Beginning of Script:
Function New-ZipFile{
PARAM
(
[String] $SrcFolder,
[String] $DestFolder,
[string] $DestZipName,
[String] $FileExtToZip,
[string] $ZipPurpose,
[string] $StoredInZipFolder,
[string] $DeleteFiles = $null
)
#$TodaysDate = Get-Date -uformat "%Y-%m-%d-%Hh%Mm%Ss.zip";
#$ZipFileName = $ZipPurpose + "_" +$DestZipName + "_" + $TodaysDate;
$ZipFileName = $ZipPurpose + "_" +$DestZipName + ".zip";

if (Test-Path $DestFolder){
## - Create Zip file or it won't work:
if (Test-Path ($DestFolder+"\"+$ZipFileName)) { del ($DestFolder+"\"+$ZipFileName) }
new-item ($DestFolder+"\"+$ZipFileName) -ItemType File
}
else
{
Write-Host "Destination Folder [$DestFolder] doesn't exist" -ForegroundColor 'Yellow';
Break;
};

## - Loads the Ionic.Zip assembly:
[System.Reflection.Assembly]::LoadFrom("C:\Program Files (x86)\DotNetZip\Ionic.Zip.dll") |

out-null;
$zipfile = new-object Ionic.Zip.ZipFile

## - AddSelectedFiles with source folder path:
## - ($false grab files in source folder) & ($true grab files & subfolder files)
$zipfile.AddSelectedfiles($FileExtToZip,$SrcFolder,$true) | Out-Null;
## - UseZip64WhenSaving, when needed, will create a temp file compress large number of files:
$Zipfile.UseZip64WhenSaving = 'AsNecessary'
$zipfile.Save($DestFolder+"\"+$ZipFileName)
$zipfile.Dispose()

If ($DeleteFiles.ToUpper() -eq 'YES'){
## - Remove all backed up files:
Write-Host "Deleting files after zip!";
get-childitem ($SrcFolder+"\"+$FileExtToZip) | remove-item
}
};

### - variables:
$DestZipName = "BackupMyTempSSIS";
$FileExtToZip = "name = *.*";
$DestFolder = "C:\MyBackupZipFolder";
$SrcFolder = "C:\TempSSIS";
$DeleteFiles = $null;
$StoredInZipFolder = "MyBackupZip\";
$ZipPurpose = "BackUp";
#or $ZipPurpose = "Save";

New-ZipFile -DeleteFiles $DeleteFiles `
-DestFolder $DestFolder -DestZipName $DestZipName `
-FileExtToZip $FileExtToZip -SrcFolder $SrcFolder `
-StoredInZipFolder $StoredInZipFolder -ZipPurpose $ZipPurpose;

## - End of Script

This is about having flexibility over what you want to do.  This is a good example how you can use an existing API with PowerShell.  As long there’s good API documentation then the rest just follows thru.

DotNETzip_APIDoc

In the above sample script you can have is Scheduled in either Task Scheduler or in SQL Server Agent. This code becomes portable.

This script the folder for the zipped file most exist or it will display a message that the folder doesn’t exist, and has the ability to delete the files after its done. (feel free to modify)

I hope you’ll find it useful!

Maximo Trinidad (MVP – Windows PowerShell)
Mr. PowerShell

Great PowerShell sessions at SQLSaturday SoFla on June 29th 2013

Yes! On Saturday June 29th, I’ll be presenting two interesting PowerShell sessions:

1. DBA Track – PowerShell Working with XML

2. SSIS Track – Integrating PowerShell in a ScriptTask component

These session come full of demos and reference information.  The important one is the  “SSIS – Integrating PowerShell in a ScriptTask component” which help you to include your already existing PowerShell script file in a SSIS solution using SQL Data Tools 2012.

I’ll be show existing exciting tools such as SAPIEN Technologies:

1. PrimalXML 2012

2. PrimalSQL 2012

3. PrimalScript 2012

4. PowerShell Studio 2012

5. Open Source .NET development tool – SharpDevelop ( and you’ll see WHY!)

Also, I will be giving away some exciting stuff you don’t want to miss.

Please come and join us in this Great SQLSaturday event.  Here’s my presentation for both sessions.