Shoot yourself in the foot

All programmers have shot themselves in the foot, I am no exception. Over the years I have shot myself in the foot so many times I have no toes left figuratively speaking that is. I accidentally almost cut off my right big toe forty-five years ago but that is another story. Programatically shoot yourself in the foot is about destroy a good program by accidentally inserting a few lines of self destructing code.

"Hey Lars, can you help us? We need to transfer a csv file from Qlikview into the Data Warehouse in real time."
"Why do you ever want to do that? Its like putting the cart before the horse. Actually it's even worse." I said.
"Yes we know, but now we have this setup and it would be of great help if we could send the csv file from Qlikview to the Data Warehouse."

I started to play with some code in the PowerShell ISE environment. It is a very nice environment to play, develop and debug code. The environment is not cleared between code executions, this is a mixed blessing, normally this is a neat feature all your variables are kept so you can just insert or add code and proceed run your program. This time however it just created problems as I commented out parts of the code between iterations so I inserted this line in top of my code to clear the ISE environment::

Remove-Variable * -ErrorAction SilentlyContinue; Remove-Module *; $error.Clear(); Clear-Host 

This worked nicely and in no time I developed a script that first FTP the csv file to the Data warehouse server the via a call to the Data Warehouse server invoked a DW workflow inserting the csv file into the Data Warehouse. I then wrapped the PowerShell code into a function 'sendFileToDW' (see below) suppling the csv file, the Data Warehouse & the workflow as parameters; now I could insert a file into the Data Warehouse by:

sendFile2DW "File.csv" "data warehouse" "workflow.xml"

Very nice except nothing happened, just some error messages. Whatever I did the parameters were blank whatever I did, being inexperienced with PowerShell functions I wrongly anticipated it was something wrong with my function setup. I tried God knows how many different parameter definitions, after hours of futile attempts I scrutinized the function code and then I found:

Remove-Variable * -ErrorAction SilentlyContinue; Remove-Module *; $error.Clear(); Clear-Host 

a bit down in the code, removing that line, the function worked as expected. This is a perfect example of shooting yourself in the foot.

function sendFile2DW {      Param ([Parameter(Position=0)] [string]$inputfile, [Parameter(Position=1)] [string]$dwuser, [Parameter(Position=2)] [string]$dwschedule)         $x = @" This scripts FTP a file to the Data Warehouse and then executes a schedule in the Data Warehouse. Parms: 1 - file to send  2 - the Data Warehouse 3 - Schedule to run in the Data Warehouse (workflow) Processing: 1 - First we do necessary configurations by initialize some variables. 2 - Then we FTP 3 - Finally we submit a schedule for execution in the Data warehouse and and wait for exection to finish Note! The Data Warehouse web server must be up and running before submitting the schedule!  Set-ExecutionPolicy -Scope CurrentUser "@      $myParms = "my parms - $inputfile, $dwuser, $dwschedule"     write-host "$myParms"          # Start of configuration     # Config for the SMTP server and mail sender & recipients for error messages     $SMTPServer = "X1"     $mailFrom = 'Qlik2DW <X2>'     $mailTo = 'X3, '     #$SMTPPort = "587"     # Config for the Data Warehouse FTP site     $FTPserver = 'X4';     $FTPmap    = 'X5';     $FTPuser   = "X6"      $FTPpw     = "X7"     # Config for the Data Warehouse web server schedule kicker     $dwwebserver = "X8"     # End of configuration. Do not touch anything below!      #Before we start, where are we?     $cwd = Get-Location      write-host "$MyInvocation.ScriptName runs in $cwd"     Remove-Variable * -ErrorAction SilentlyContinue; Remove-Module *; $error.Clear(); Clear-Host # wiping out vars :(     write-host "FTP upload file=$inputfile , FTP = $FTPserver, $FTPmap, $FTPuser"     $FileName = [System.IO.Path]::GetFileName("$inputfile");     $FTPfileName = $FileName     $FTPfile = $FTPserver + "/" + $FTPmap + $FTPfileName      write-host "Start uploading file $inputfile to FTP as $FTPfile"     #write-host "ftp://${FTPuser}:${FTPpw}@$FTPserver/$FTPfileName"     $client = New-Object System.Net.WebClient     $client.Credentials = New-Object System.Net.NetworkCredential("$FTPuser", "$FTPpw")      $uri = New-Object System.Uri("ftp://$FTPfile")           try {         $client.UploadFile($uri, $inputfile)         $client.close     } catch {         $_.Exception.Message         $emsg = "The FTP upload failed aborting..., please check!"         Write-Host "$emsg"         Send-MailMessage -From $mailFrom  -To $mailTo -SmtpServer $SMTPServer -Priority High -DeliveryNotificationOption OnFailure  -Subject 'Error' `            -Body "$_.Exception.Message `n $emsg `n $myParms"            Exit     }      write-host "FTP upload of $inputfile done"      write-host "Submitting schedule $dwschedule to $dwwebserver for execution in the Data Warehouse"      try {         $wr = Invoke-WebRequest -Headers @{"Cache-Control"="no-cache"} http://$dwwebserver/dw/$dwuser/$dwschedule -method get     } catch {         $_.Exception.Message         $emsg = "The DW Web server may be down, please check!"         Write-Host "$emsg"         Send-MailMessage -From $mailFrom  -To $mailTo -SmtpServer $SMTPServer -Priority High -DeliveryNotificationOption OnFailure  -Subject 'Error' `            -Body "$_.Exception.Message `n $emsg `n $myParms"            Exit     }     $wr.InputFields | Where-Object {         $_.name -like "*"     } | Select-Object Name, Value     $dwpid = $wr.Content     $dwstsdesc = $wr.StatusDescription     $dwstscd = $wr.StatusCode     $wr.close     Write-Host "Executing the schedule $dwschedule in the Data Warehouse job PID=$dwpid Status=$dwstsdesc ($dwstscd)"     If ($dwstscd -ne 200) {         $emsg = "Oops, An unexpected error occurred! (Status=$dwstscd)"         Write-Host "$emsg"         Send-MailMessage -From $mailFrom  -To $mailTo -SmtpServer $SMTPServer -Priority High -DeliveryNotificationOption OnFailure  -Subject 'Error' `            -Body "$emsg `n $myParms"           Exit      }     Write-Host "Waiting for schedule $dwschedule pid=$dwpid to finish, content=$dwpid, desc=$dwstsdesc, statuscd=$dwstscd"     do {         Start-Sleep -s 1         $wrp = Invoke-WebRequest http://$dwwebserver/pid/$dwpid -method get     #    Write-Host $wrp.Content         $wrp.InputFields | Where-Object {             $_.name -like "*"         } | Select-Object Name, Value     } while ( $wrp.Content -eq 0)     $wrp.close     Write-Host "Done, the schedule $dwschedule is executed in the Data Warehouse"     #Pause }