SSIS Task Variables

In some current Testing I am doing work with SSIS.  One task imports data into a database that we baseline for future Test Cases, but to get the data in I need to modify some of the dates.  With SSIS there is an option to adjust data using Derived Columns, with that it's possible to adjust data using Task Scoped Variables; I'll have to find a reference but using project scoped variables did not work initially.

Basically the variables were created this way:

1. Gave a descriptive name (need to have those so you know what to look for later!)
2. Scope, was selected to be the Task being worked on, which was easy since the project only had one task for data loading.
3. Data Type, since it was for Dates I made this DateTime
4. Value, just hit enter/return here and it took the current date and time
5. Expression, this is the real work here
1. For Now this was just left as the default - GetDate()
2. For a Year I made an expression - DATEADD("Year",1,(GetDate()))
3. Same adjustments for Adding Minute or Month, just adjust Year

When using these, in the Derived Columns select the Expression column and in the upper right panel there is a tree for Variables and Parameters.  Select the User: and this will update the value for that column when the Derived Columns task runs.

Learning about DateAdd came from the Microsoft Site

Why History really relates to Testing

I am a History buff.  Of course I also went back to school and did a major in it, getting my Bachelor's from Boston University while still working in Software.  I was going to do a CS degree but honestly, much as I like it, programming during the day and for homework didn't really suit me.  After some discussion with my wife I ended up changing to History, since it was something I loved, I think it was a great decision since I got exposed to subjects and topics I never would have thought to take on my own.  From those I learned a lot about how to analyze situations and topics, do research and apply that to the question or topic at hand; something I do a lot of while Testing.

Testing IS History.  Regression Tests are a way of compounding a Historical Record of Fact that says, these things happened and if we want to prevent them we can do THIS or THAT.  Something that in History we often table about, the What If format is not only a plot line from Fiction novels but sometimes is a way of viewing an event from a certain perspective.  Of course as our understanding of events increases so does the Historical Record and our understanding of it, I find that Regression Tests are the same way.  The more I understand a certain issue, or feature, the more I can improve my test case and get the confidence level of the product higher.

When scheduling I rely on my knowledge of prior events, but being the person I am I document everything as I go along.  This gives me a record to use and base my future estimates on rather than giving my "gut feel" for a specific project or set of features.  When I did releases we often put times for specific steps to take, this way in long releases we were able to say when certain people were needed to help, or to even frame the maintenance window needed.

These are just two examples, but very deep and broad ones.

How do you use and document your prior knowledge on events and tests?  Do you document your test code, like a historical record, so others who come later can figure out what you were doing?

Sauce Connect Script

I am currently working with Sauce Labs and using their Cloud Based testing to work with some automation, what I wanted first was an easy way to handle starting up the tunnel to Sauce Labs.  This was a jar file so I tried a few other methods to set the environment up the way I want, but considering that most environments I am working with are Windows I figured may as well use the old stand by of PowerShell.

Here is my current version of the script, this just checks to see that the environment is set up so I can output the log files in the places I want, and checks if a job is running then just continues on and uses the existing one - for now.  I heavily comment code when I first write it, so it should be enough to answer most questions, if not comment here and let me know!

----

# Sauce Labs Connect script
# Description:
# This script checks and sees if there is an existing Job that has an open tunnel
# to Sauce Labs in the current session; because of the way PowerShell works you
# cannot easily detect what is running in another session.  Basically this script
# was intended to generate the necessary Tunnel and run the test scripts with
# fewer keystrokes
# Author: Michael Furmaniuk
# Last Updated: April 16, 2014

# Command line values, so there is no reliance on the Username and Access Key
# being set in the environment or in the configuration files (although if they already are...)
param([string]$u, [string]$k)
Set-StrictMode -v 2

#######################################
# Check and make sure some of the specific needs are met
# Sauce Connect
[string]$jobName = "sauceConn"
[string]$rootPath = ""
[string]$sauceLog = "sauceLog.log"
[string]$sauceReady = "sauceReady.log"
[string]$sauceJar = "C:\Sauce-Connect-jar\Sauce-Connect.jar"
# If necessary the following could be completed to make this easier to run, otherwise these are
# command line arguments given as $u $k
# [string]$u = ""
# [string] $k = ""

#######################################
# Start the Sauce Jar
function start-SauceConnectJar()
{
<#
.Synopsis
This function starts the Sauce Connect Jar utilizing some known and command line parameters
.Description
This function starts the Sauce Connect Jar utilizing some known and command line parameters
#>
[CmdLetBinding()]
Param(
[Parameter(Mandatory=$true)][string]$sauceLog,
[Parameter(Mandatory=$true)][string]$sauceReady,
[Parameter(Mandatory=$true)][string]$sauceJar,
[Parameter(Mandatory=$true)][string]$u,
[Parameter(Mandatory=$true)][string]$k
) #end param
Process
{
# Let's check and see what drives and temp files we have
if (Test-Path "C:\TEMP") {
$rootPath = "C:\TEMP"
} elseif (Test-Path "D:\TEMP") {
$rootPath = "D:\TEMP"
} else {
Write-Host "I can't seem to detect any disk drives here with a TEMP directory, that is a problem.`n"
exit
}
# Build up the file paths
$sauceLog =  $rootPath + "\" + $sauceLog
$sauceReady = $rootPath + "\" + $sauceReady
# If these already exist we want to remove them
if (Test-Path $sauceLog) {
Remove-Item $sauceLog
}
# Note: if this call fails then there is an existing job in another session
# need to somehow handle this and fail gracefully: this is a TODO
if (Test-Path $sauceReady) {
Remove-Item $sauceReady
}
# Start Sauce Connect and see what is returned for the process
# Build up the latter part of the script block
$commandLineOption = "-l $sauceLog -f $sauceReady -d"
# Now for the full command line
$scriptblock = [scriptblock]::Create("java -jar $sauceJar $u $k $commandLineOption")

# Now let's start this as a Job, next line is for debugging
# Write-Host "Starting Sauce Connect with: $($scriptblock).`n"
# Should now be suppressing the start text
$output = Invoke-Expression "start-job -name $jobName -ScriptBlock { $scriptblock } 2>&1"

# Check for the Ready File
while (!(Test-Path $sauceReady)) {
Write-Host "Sauce Connect is still starting...`n"
sleep(10)
}
# Next statement is for debugging
# Since you only get a Tunnel ID when the connection is complete using this as a defining
# point to know the tunne is up, next statement was for debugging
# Write-Host "Sauce Connect should be up...getting the Tunnel ID.`n"
# Now acually getting the Endpoint/Tunnel ID as a verification step
get-SauceEndPoint $sauceLog
}
}

#######################################
# Get the endpoint ID from the log
function get-SauceEndPoint()
{
<#
.Synopsis
This function scans the initial Sauce Connect log file for the EndPoint ID to pass back
.Description
This function scans the initial Sauce Connect log file for the EndPoint ID to pass back
to the User through the console, so it can be used by the User to access the active Tunnel.
At some point this may be used for further automation
#>
[CmdLetBinding()]
Param(
[Parameter(Mandatory=$true)][string]$sauceLog
) #end param
Process {
# Now that the Connection is up, and the Ready File is in place
# read the log file to get the Tunnel ID or endpoint in the case of Java
$runningLog = Get-Content $sauceLog
# Run a regex to get the endpoint line, only one in the file at a time
# when this script runs, then print this out so it can be used (or saved for something else later)
$myMatch = $runningLog -match "endpoint ID:\s.*$" | %{$_ -split "ID:\s"}
# Next statement was a way to message that the Tunnel is up, if not needed comment out
Write-Host "Endpoint ID this time is: $($myMatch[1])"
}
}

#######################################
# The Sauce branch depending on what is running,
# I can either start a new Job or shut it down as/if necessary
$jobs = Get-Job 2>&1
if ($jobs) {
if ($jobs.State -eq "Running") {
# There is an existing Job, either we want to stop it and restart or drop out
# Right now going with using an existing Tunnel
Write-Host "An existing job is running, quitting for now."
Write-Host "...but getting the EndPoint ID to be useful."
get-SauceEndPoint $sauceLog
exit
}
elseif ($jobs.Name -eq $jobName -and $jobs.State -eq "Completed") {
Write-Host "Found $($jobs.Name) that was $($jobs.State) getting rid of it...`n"
Remove-Job $jobName
# Now that the previous one is gone, let's move on
Write-Host "Removed the old job, so starting a new Tunnel.`n"
start-SauceConnectJar $sauceLog $sauceReady $sauceJar $u $k
} else {
Write-Host "Weird, found a job but its not running, it's $($jobs.State).`n"
}
} else {
# Shouldn't be any other states to worry about at this point
# so just start the Jar file and move along
Write-Host "Nothing running, so starting the Tunnel.`n"
start-SauceConnectJar $sauceLog $sauceReady $sauceJar $u $k
}