Passing Parameter to a PowerShell executable – Part 2

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”

Sample Dummy CompanyData.txt Source file

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.

Unmatching records causing SSIS package to 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.

Dragging the RedArrow from Source to the second Destination Component

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“.

Change both "Error" and "Truncation" to "Redirect Row"

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

Verify all fields has "Redirected 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.

Accept Default Error table name

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.

4 bad Record went into our Err 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:

USE [Developer]

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




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


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: Denali CTP3 already has it). max

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

# =============================================================
# 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")`

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

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

$sqlqry1 = @"
USE $Arg2

	[Flat File Source Error Output Column] as DataSkipped
  from $tblName

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

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

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

$sqlqry2 = @"
USE $Arg2

INSERT INTO [dbo].[CompanyData_SkippedRecords]
Write-Host $sqlqry2

		Invoke-Sqlcmd -ServerInstance $Arg3 -Query $sqlqry2;
	} Until($recCount -eq $rdSkipData.Count);

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: – “ 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:

Create variable to be use as parameters to our PowerShell 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:

Property Expression Editor - Select Arguments

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:

Building your Input Arguments

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:

Checking my Skipped data

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:

Passing Parameter to a PowerShell executable – Part 1

Creating a PowerShell Executable with Parameter(s)

Recently, I’ve been creating some PowerShell script, and intergrating it into my SSIS solution.  You may ask, but how??  Simple, if you a product such as SAPIEN’s PrimalForms (or, PrimalScript), you can create an executable out of your script.  Yes!! You can create an executable file of your PowerShell script.  But, not until recently, I didn’t need to pass parameter(s) my  compiled version of my script(s).

Let’s start with some very basic sample of how you create a PowerShell executable script that will accept parameters and display the results on the PowerShell Console prompt.

First the Sample Script, my of “HelloWorld” executable -“HelloWorldExec.ps1”

# Created with: SAPIEN Technologies, Inc., PrimalForms 2011 v2.0.9
# Created on:   8/17/2011 6:08 PM
# Created by:   Max Trinidad
# Organization:  ISO Group, Inc.
# Filename:      HelloWorldExec.ps1

Param([string] $firstName, [string] $lastName)
Write-Host "Hello Work!! I'm $firstName $lastName" `
    -BackgroundColor Black -ForegroundColor Yellow

#end of script

Now, we need to compiled this script using “Script Packager” under the “Export” tab in PrimalForms 2011.

Open 'Package File"

We are going to select to build the executable for a “WIN32 Command Line” because this script is consider a Console Application and there’s no windows form in it. We click in the “Build” button to cerate the EXE. This exe file will be built in the same folder where your script are stored.

Make sure to select WIN32 or WIN64 Command line option

Let’s open my PowerShell Console, change directory to where my executable is located, and run it.  Test the executable in both DOS and PowerShell command prompt:

Testing PowerShell executable in both DOS & PowerShell Console

Good!  As you can see this is a very simple script.  Basically, this script doesn’t need to be a function in order to use the Param() keyword to accept two input arguments: $firstName, and $lastName.

Now, we are ready to build another executable so we can use it on our next blog Part-2PowerShell Integration to a SSIS solution“.

For more information about SAPIEN’s PrimalForms 2011, click on the link:

Great SQL Saturday South Florida Event – PowerShell included!!

Yes!!  It was a great event with over 400 people showing up, and only two PowerShell Sessions included.  Thanks to all SQL Saturday organizers for all the efforts in putting this events together.  I’m honor to be part of it.  Thanks Scott and Herve!!

Well, here’s both presentations: Session 1 – Let’s Talk About SQL PowerShell, Session 2 – Extendign your T-SQL with PowerShell, and more samples:

Many THANKS!! to all who attended my sessions, and I know that you all were asking for more at the end.  This is just an example that there’s a lot to talk about PowerShell and every little time to do it.  But, don’t worry!

I’m in the process of setting up some livemeeting event every on the third week of the month at 11:30am(EST).  It will a “PowerShell Code Snippet” 3o minutes demo with the purpose of showing block of code by myself and our PowerShell community.  Hopefully, this will speed up your learning by reusing these block of code.

Stay Tuned to this blog site for more information.

Just forgot… Congrats to all the winners how found marking on the flyers I provided.  I gaveaway 2 SAPIEN PowerShell V2 ebooks (X), 2 PluralSight One Month Training  Subscription(PL), and 2 Diskeeper Pro Premier Defrag software for desktop (D).

Happy PowerShelling!!