Category: PowerShell

Here’s a simple PowerShell SMO code that shows you How-To change a SQL user password.  Keep in mind, SMO needs to be installed and the assemblies loaded before using this code.

To load SMO, you can:

1. Install the SQLPS Module (is using SQL Server 2012 “Denali”), or Chad’s Miller SQLPS module for SQL Server 2008/R2.

Import-Module SQLPS -DisableNameChecking

2. Or, Load the SMO Assemblies using the PowerShell V2 “AddType” command: (but carefull is you have multiple SQL Server versionsin the same box)

Add-Type -AssemblyName “Microsoft.SqlServer.Smo”

3. Or, use the still reliable the old PowerShell V1 load assemblies line using:

[reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”)

In the following example show how to connect to a SQL Server using both the default Windows Authentication (with high Administrator privileges already set), or SQL Server Authenbtication to later change a user password.

*Hint*: If you want to include special characters, you need to use the single qoutes or PowerShell will think that it’s a variable name.

As you can see,  with just a few line of PowerShell SMO code you can start orchestrating a solution that can be applied to your SQL Server environments.

Just try it!

I’m excited to bring this new topic to #SQLSat86 Tampa for the BI community.  So, here’s a brief rundown of what I will be covering on Saturday, November 5th morning session:

Topic: SSIS – Analyzing your data integrating PowerShell

*Note: This is not an all PowerShell Session but it plays a big role in this SSIS solution.

What will be covering:

1. Some basic to intermediate SSIS and PowerShell.
2. How to pass arguments between PowerShell and SSIS steps.
3. How to use PowerShell to assist in analyzing our tables and/or Data.
4. How to Integrate PowerShell in our SSIS Solution (when needed).
5. Tool available to allow these two technologies to integrate in our ETL solution.

This is going to be an ALL DEMO session.

Requirements:

1. Have some interest in PowerShell.
2. Some basic SSIS ETL experience.
3. Willing to be open to new technologies.
4. Most Important: No PowerShell experience is not required.

I will Demo:

1. Passing Arguments between PowerShell Applications in SSIS.
2. Work with both Script Task and Script Components.
3. Use of both VB and C# (CSharp) .NET Scripting code.
4. Some PowerShell.

I hope yopu will enjoy this presentation

Today, while help @meltondba with his SQLPS question on the enumerating jobs history, @LaerteSQLDBA provided a oneliner to provide this result.  There’s one concern, should we use SQLPS instead of SMO.   For this answer I’m going to point out MSDN article regrading the future of SQLPS in upcoming SQL Server releases: http://msdn.microsoft.com/en-us/library/cc280450(v=SQL.110).aspx

This article states that “… This feature will be removed in a future version of Microsoft SQL Server.  Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use the sqlps PowerShell module instead. For more information about the sqlps module, see Import the SQLPS Module. …”

So, during this exercise I found out that I can out with the same number of line for either SQLPS and SMO.  Both giving me the same results.

Here’s some picture ilustrating basic code snippet of both SQLPS and SMO to get some SQL Server Jobs information:

This pictures shows, it look simple enough.  So, if you’re building SQLPS scripts, you can easily start transitioning your code to SMO.

Although, PowerShell give the whole remoting experience, you may still need to ask you network administrator to let you configure remoting on all the machines you need to access.  Of course, unless you hold the keys of the kingdom.  But, it is much  simpler without PowerShell Remoting.

When you use the SQLPS Module it automatically let you connect to any of your SQL Servers on your network.  So, YES!! As long as you have permission to manage these servers, SQLPS already gives you the ability to remotely connect to other machines.  There’s no need to setup the Remoting with  Enable-PSRemoting or Set-WSManQuickConfig commands.

Simple Remoting #1

So, How can I change from my SQL Server local prompt to another instance?  Here’s an example how:

1. First load the SQLPS Module:

[sourcecode language=”powershell”]
Import-Module SQLPS -DisableNameChecking
[/sourcecode]

2. Confirm you have the SQL Server drive available:

[sourcecode language=”powershell”]
Get-PSDrive SQL*
[/sourcecode]

3. Let’s navigate to your SQL Server Instance:

[sourcecode language=”powershell”]
cd SQLServer:\SQL\Your-Machine-name\Default  #or

cd SQLServer:\SQL\Your-Machine-name\Your-InstanceName
[/sourcecode]

4. Now you can continue and navigate through your SQL Server drive like in DOS filesystem.

[sourcecode language=”powershell”]
Dir Databases -force
[/sourcecode]

5. Finally, to remote connect top another session just change the “Your-Machine-name”, and your “Your-InstanceName”.

Simple Remoting #2

One thing you need to understand, after you load SQLPS module, now you have access to all SMO (SQLServer Management objects) .NET Classes. This will help you build short and very effective .NET that extend the scripting of PowerShell.  Also, you don’t need to be at the SQLServer: drive to use it.

Here’s a basic example using SMO:

1. First load the SQLPS Module:

[sourcecode language=”powershell”]
Import-Module SQLPS
[/sourcecode]

2. Create a variable to hold your local SQL Server objects:

[sourcecode language=”powershell”]
$MySQL = new-object Microsoft.SqlServer.Management.Smo.Server
[/sourcecode]

3. Now that your .NET object has been create you need to see all what you can grab from it:

[sourcecode language=”powershell”]
$MySQL | Get-member | Out-GridView
[/sourcecode]

4. I decided to grab the “Information” property so I can display all the information, and verify that this is my local SQL instance:

[sourcecode language=”powershell”]
$MySQL.Information
[/sourcecode]

5. Finally, to remote connect to another machine, I just add the SQL Server InstanceName to the line creating my variable:

[sourcecode language=”powershell”]
$MySQL = new-object Microsoft.SqlServer.Management.Smo.Server "Your-Machine-name"Â
[/sourcecode]
Or
[sourcecode language=”powershell”]
$MySQL = new-object Microsoft.SqlServer.Management.Smo.Server "Your-Machine-name\Your-Instancename"
[/sourcecode]

Keep in mind, you can another variable holding your SQL Server name with the instancename and do something like:

[sourcecode language=”powershell”]
$SQLInstanceName = "Your-Machine-name\InstanceName"
$MySQL = new-object Microsoft.SqlServer.Management.Smo.Server $SQLInstanceName
$MySQL.Databases[$SourcedbName].tables | sort -desc dataspaceused | select Parent, `
@{Label="DisplayName";Expression={$_.Schema+"."+$_.Name};} | `
sort DisplayName;
[/sourcecode]

Again, no need to setup PSRemoting.

Simple Remoting #3

And this is the simplest of all, if you like to reuse your T-SQL scripts.  This will be your most used PowerShell SQLPS cmdlet “Invoke-SQLcmd“.

1. Load the SQLPS module:

[sourcecode language=”powershell”]
Import-Module SQLPS
[/sourcecode]

2. Just start using the SQLPS “Invoke-SQLcmd” command: (basic form)

[sourcecode language=”powershell”]
Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance "."  #or

$SaveResult = Invoke-Sqlcmd -Query "SELECT * FROM [AdventureWorks].[dbo].[AWBuildVersion];" -ServerInstance "Your-Machine-name\InstanceName"

$SaveResult
[/sourcecode]

Again, there’s no need to be at the SQLServer: drive in order for this to work.  As you can see, with just a few lines of code you can connect to another SQL Server instance.

Go ahead and try it!

[sourcecode language=”powershell”]
#========================================================================
# Created with: SAPIEN Technologies, Inc., PrimalForms 2011 v2.0.12
# Created on: 8/31/2011 6:33 PM
# Created by: Max Trinidad
# Organization: PutItTogether
# Filename: DivideNow3.ps1
# http://thepowershellguy.com/blogs/posh/archive/2008/05/20/hey-powershell-guy-how-can-i-run-a-powershell-script-from-cmd-exe-and-return-an-errorlevel.aspx
# http://msdn.microsoft.com/en-us/library/system.environment.aspx
#========================================================================
Param([Int] $arg1, [Int] $arg2)
Try
{
[Double] $result = $arg1 / $arg2;
}
Catch
{
#Write-Host "Error in function";
$err1 = 1;
}
Finally
{
if($err1 -eq 1)
{
## – Custom Exit Code:
Exit 99;
}
Else
{
Write-Output $result;
Exit 0;
}
}

[/sourcecode]

Sample Script 2 – DivideNow2.ps1 – using “Exit(“N”).
[sourcecode language=”powershell”]
#========================================================================
# Created with: SAPIEN Technologies, Inc., PrimalForms 2011 v2.0.12
# Created on: 8/31/2011 6:33 PM
# Created by: Max Trinidad
# Organization: PutItTogether
# Filename: DivideNow2.ps1
# http://thepowershellguy.com/blogs/posh/archive/2008/05/20/hey-powershell-guy-how-can-i-run-a-powershell-script-from-cmd-exe-and-return-an-errorlevel.aspx
# http://msdn.microsoft.com/en-us/library/system.environment.aspx
#========================================================================
Param([Int] $arg1, [Int] $arg2)
Try
{
[Double] $result = $arg1 / $arg2;
}
Catch
{
#Write-Host "Error in function";
$err1 = 1;
}
Finally
{
if($err1 -eq 1)
{
## – Custom Exit Code:
Exit("99");
}
Else
{
Write-Output $result;
Exit("0");
}
}

[/sourcecode]

Results for DivideNow2.ps1

Sample Script 1 – DivideNow.ps1 – using .NET “[Environment]::Exit(“N”)”.
[sourcecode language=”powershell”]
# Organization: PutItTogether
# Filename: DivideNow.ps1
# http://thepowershellguy.com/blogs/posh/archive/2008/05/20/hey-powershell-guy-how-can-i-run-a-powershell-script-from-cmd-exe-and-return-an-errorlevel.aspx
# http://msdn.microsoft.com/en-us/library/system.environment.aspx
#========================================================================
Param([Int] $arg1, [Int] $arg2)
Try
{
[Double] $result = $arg1 / $arg2;
}
Catch
{
#Write-Host "Error in function";
$err1 = 1;
}
Finally
{
if($err1 -eq 1)
{
## – Custom Exit Code:
[Environment]::Exit("99");
}
Else
{
Write-Output $result;
[Environment]::Exit("0");
}
}

[/sourcecode]

Results for DivideNow.ps1

This is the results we want to get.  Both DOS and PowerShell will show the correct assigned Exit Codes.  This script and executable will work and can be integrated in an SSIS package.

Bonus Script – Executing a PS Script from PowerShell.exe in the PowerShell Console to execute DivideNow.ps1

[sourcecode language=”powershell”]
#========================================================================
# Created with: SAPIEN Technologies, Inc., PrimalForms 2011 v2.0.12
# Created on: 9/2/2011 9:21 AM
# Created by: Max Trinidad
# Organization: PutItTogether
# Filename: DivideNow_test.ps1
#========================================================================

# .\DivideNow.ps1 40 5 – This will execute and close PowerShell immediately
# .\DivideNow.ps1 40 0 – This will execute and close PowerShell immediately

# But if I execute with PowerShell.exe
PowerShell -noexit -command { `
cd ‘C:\Users\Max\Documents\SAPIEN\PrimalForms 2011\Files’; `
& ./DivideNow.ps1 40 5}

$LastExitCode

PowerShell -noexit -command { `
cd ‘C:\Users\Max\Documents\SAPIEN\PrimalForms 2011\Files’; `
& ./DivideNow.ps1 40 0}

$LastExitCode
[/sourcecode]

Happy PowerShelling

Using a PowerShell executable w/Param() in SSIS

Here’s where the fun begins.  It took me a few hours to figure something out in order to my SSIS package pass some values into my PowerShell executable.  So, here’s a sample solution where I found out that my Flat file source record didn’t match the record layout provide by another Internet source.  Yes, believe or not, sometimes documentation out there might be way out of day.  But, Thanks to SSIS “..Source Error Output“, you can redirect these records error to a destination object.  Then you can later analysis these bad records.

So, if you work creating SSIS packages, then everyone should know how to use the “Data Flow component” to help you define your data Source, and Destination objects.  Now, in cases when you need to isolate records that are causing your package to fail, then you need to add a second Destination object to deal with them later.  In my example, my second Destination object will be to a new SQL table called “CompanyData_SkippedRecords“.

Here’smy sample Text file to be process by my SSIS package: “CompanyData.txt”

Notice, the first line are the columns, but some of the data in this file won’t match the column layout.    If I run this solution with the Source and Destination objects defined, this package will fail.

Now, we can add your another Destination component to isolate these unmatching records.  Connect the Source component by dragging the “Red Arrow” to the second Destination Component handling the errors.

A “Configure Error Output” windows will popup, change the “Set this value to selected cells:” to  “Redirect row“.  Then, highlight all fields in both “Error” and “Truncation” columns, and Click “Apply” to change the “Fail Component” to “Redirect row“.

Click “OK” when you confirmed that all fields has “Redirect row“.

These changes are in place for our Flat File Source “Error Output” section.

Now, we need to complete setting up out second destination component labeled “Error Skipped Records Destination” where our Error Records will be dropped into our new SQL table.

Open our Error Destination component.  For now, we are going to use the default, the new table will be named “[Error Skipped Records Destination]”.  So, all bad records will be stored in this table.

Then, click “OK” to exit.

At this step we are done with our Import and Error/Mismatch records handling, and we are ready to run our solution.  At the end of this step will have bad records table.

Here’s our good data:

And, this is Error Records table:

Now, the next step, I need to a table to consolidate all my mismatched records.  I will be reading from my “[Error Skipped Records Destination]” and selecting the “[Flat File Source Error Output Column]” field as “DataSkipped” into a new table called “CompanyData_SkippedRecords“.

Here’s the new table layout:

[sourcecode language=”sql”]
USE [Developer]
GO

/****** Object: Table [dbo].[CompanyData_SkippedRecords]
Script Date: 08/18/2011 12:27:15
*****/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[CompanyData_SkippedRecords](
[DataSkipped] [text] NOT NULL,
[ErrRecordsLength] [varchar](7) NOT NULL,
[TotalFieldsPerRecords] [varchar](5) NOT NULL,
[FileGroupSource] [varchar](4) NOT NULL,
[ProcessDate] [datetime] NOT NULL,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO
[/sourcecode]

As you may notice, this table is very different than the we one createdto store the errors.  And here’s where we integrate our PowerShell script solution into our SSIS Package.

PowerShell Script

In this new table out of the 5 fields 4  will be genrated by PowerShell , and two of them are coming from parameters:

1. DataSkipped – comes from the our Err table.
2. ErrRecordsLength – Count of the length of each records skipped.
3. TotalFieldsPerRecords – Count the number of fields per records (using the delimiter -1).
4. FileGroupSource – Denominates the type of records that was imported (ie.Comp).
5. ProcessDate – Date that this Err table was process.

The purpose of this PowerShell executable it to generate this additional fields to help analyzing what’s wrong with my Text file.  This process will read one Err Record at a time, generate the addition values, and insert into the new table.

One requirement: You need to have the SQLPS module installed in your “MyDocument\WindowsPowerShell\Modules” folder were the user is running this SSIS package, or will not run. If you’re usign SQL Server 2008 R2, then download the SQLPS module from Chad’s Miller blogsite:http://sev17.com/2010/07/making-a-sqlps-module/(SQL Denali CTP3 already has it). max

Here’s the Script (don’t be scare!), to be compiled with PrimalForms 2011:

[sourcecode language=”powershell”]
# =============================================================
# Microsoft PowerShell Source File — Created with SAPIEN Technologies PrimalScript 2011
# NAME: AnalyzeSkippedRecords.ps1
# AUTHOR: Max.Trinidad ,
# DATE : 8/18/2011
#
# COMMENT: PowerShell Script to Analyze the Data Skipped for processing and update the Skipped
# table with additional information.
# =============================================================
PARAM([String] $Arg1, [String] $Arg2, [String] $Arg3)
#Imput: $Arg1="Err*"; $Arg2 = "Developer"; $Arg3 = "YourComputer"

Import-Module SQLPS -DisableNameChecking;

[DateTime] $ProcessDate = (get-date).month.ToString("00")+"/"+(get-date).Day.ToString("00")`
+"/"+(get-date).Year.ToString()

$MySQL = new-object(‘Microsoft.SqlServer.Management.Smo.Server’) $Arg3
$SchemaTableList = $MySQL.Databases[$Arg2].tables | where {$_.name -like $Arg1} | `
select @{Label="Schema_Tablename";Expression={$_.Schema+".["+$_.Name+"]"};};

foreach($table in $SchemaTableList)
{
$tblName = $table.Schema_TableName;

$sqlqry1 = @"
USE $Arg2
GO

select
[Flat File Source Error Output Column] as DataSkipped
from $tblName
"@;

$rdSkipData = Invoke-Sqlcmd -ServerInstance $Arg3 -Query $sqlqry1;

$recCount = 0;
Do
{
$getInfo = $rdSkipData[$recCount] | select DataSkipped., `
@{Label="ErrRecordslength";Expression={($_.DataSkipped).Length};}, `
@{Label="TotalFieldsPerRecord";Expression={(($_.DataSkipped).split("^")).count};};

$DataSkipped = $rdSkipData[$recCount].DataSkipped.ToString().Replace("’","").Replace(‘"’,”).Trim();
$ErrRecordsLength = $getInfo.ErrRecordsLength;
$TotalFieldsPerRecord = $getInfo.TotalFieldsPerRecord;
$FileGroupSource = $Arg1;

$sqlqry2 = @"
USE $Arg2
GO

INSERT INTO [dbo].[CompanyData_SkippedRecords]
VALUES(
‘$DataSkipped’
,’$ErrRecordsLength’
,’$TotalFieldsPerRecord’
,’$FileGroupSource’
,’$ProcessDate’
)
GO
"@;
Write-Host $sqlqry2

Invoke-Sqlcmd -ServerInstance $Arg3 -Query $sqlqry2;
$recCount++
} Until($recCount -eq $rdSkipData.Count);
}
[/sourcecode]

Notice this script was meant to search for multiple error tables based of the type of File groups (ie. ADB1, ADB2, and ADB*).  Now, using the steps from Passing Parameter to a PowerShell executable – Part – 1 on the previous blog, i compiled my script and create my executable.  Keep in mind, the you will need to move this executable to the machine you are running this SSIS Package.  Thanks to SAPIEN for this Great product!

Create SSIS Variables for input Parameters for the PowerShell Executable

This part took me a few hour to figure out how to pass parameters using the SSIS variables in combination with “Expressions”.  Yes!!  That was the keyword “Expressions”.  I couldn’t fine a blog out there that will properly explain this process.  Just one that I will give some credit because this keyword got my attention: http://dennyglee.com/2006/05/12/ssis-global-variables-and-execute-process-task/ – “..you can modify the Execute Process Task at the point of the Property Expression attribute..”.  Unfortunately, thsi blog didn’t show a step-by-step on how this done.  But, lucky you, I’m blogging about it.

Here’s how is done:

Create three variable that will hold some parameters values to be injected into the executable:

Now, we add the “Execute Process Task” component, connect the “Data Flow Task”, and double-click on the “Execute Process Task”.  In the “Execute Process Task Editor”, go to the “Process” section and in “Executable” add the executable name “AnalyzeSkippedRecords.exe”.

And now,… to setup your input parameters,  Go to the “Expressions” section:

Here’s where you define the input Argument(s) for your executable(s).  Under “Property expressions”, you select the property “Argument”, and then click on the “…” to build the expresions:

Yes!! This is the money maker:  ( there’s a space between the double-quotes )

@[User::ErrorGroup] + ” ” + @[User::SourceDatabase] + ” ” + @[User::SQLInstance]

Now, click “OK” twice to save the changes, and  we ready to run our full SSIS package.

Success!! This solution works.

Check my “CompanyData_SkippedRecords” table:

Now, I can keep working on analyzing this data with PowerShell, and/or other tools.

Couple of Pointers:

In the “Execute Process Task Editor”, you can troubleshoot your executable session if you leave the setting “WindowStyle” to Normal.  This way you can see the console popup. Then, when everything is working correctly, change it back to Hidden“.

Also, if you’re having some problem with the PowerShell executable failing, you can add the “Write-Host ‘Pause’; Start-Sleep -second 10;” oneliner command to pause the session and give you time to see the error when leaving the “WindowsStyle” as Normal.

Download this SSIS_PowerShell_sample solution at: