Feeds:
Posts
Comments

Archive for February, 2012

I was recently working on a Windows Server in my office when I noticed it had not been rebooted in a while. That got me to thinking, how many other servers in my environment had gotten in under the radar and were in need of a reboot. So, I decided to try my hand at writing another PowerShell Script. I started with a function called Get-RebootTime which is part of a module called BSonPosh. This module was created by Brandon Shell who is a MVP and who has his own blog at http://bsonposh.com.

What I was looking to do was to have a list of the servers I wanted to check on a regular basis and have it automatically check each server and write the results to a csv file. That way I could review the file at my leisure and sort it by oldest reboot date.

So, I set about creating my script.

First I created a variable to contain my list of servers.

$Servers = "DC1", "FS1", "RPTS1", "SQL1"

Then I wrote a ForEach statement to run a cmd for each item in the $Servers list.

ForEach ($Computer in $Servers) { Get-RebootTime -ComputerName $Computer -Last }

When I ran this in my PowerShell editor it produced the desired results but in a simple list.

RebootTime ComputerName
7/27/2011 1:39:12 PM DC1
2/10/2012 9:16:28 AM FS1
2/11/2012 11:40:59 AM RPTS1

This was great but the output was on the screen and not in a file that I could look at later and it wasn’t in a format I could manipulate. So, next step was to look into outputting the info into a file.

I tried using the Export-Csv cmdlet like this

ForEach ($Computer in $Servers) { Get-RebootTime -ComputerName $ComputerLast | Export-Csv ‘C:\Greg\boottimes.csv’ -notype}

This worked, or so I thought, but in reality all it did was each time overwrite the same file with an entry.  So that when I viewed the file it only showed the LAST server that the script queried.

That wouldn’t do…

So, I started looking into how to get the results of each Get-RebootTime cmd into an array so I could later output the array into csv. 

What I came up with was this:

First I created an empty array called $results to hold all my output.

$results = @()

I also created a variable for the path and name of the output file.

$outputFile = ‘C:\Greg\boottimes.csv’

Then I changed my script to use New-Object cmdlet to create a new object called $result and using the Add-Member cmdlet I was able to add members to my object in the form of the values of the server name and the reboot time each time I looped through. Then I would take the object and add it to my $results array. When the loop was completed I could then use the Export-Csv cmdlet to output $results all at once into a csv file.

Here is what the finished script looked like:

# ServerBootTimes.ps1
# Written by Greg Caporale Febrary 28, 2012
#
# Determines the last date a list of computers was rebooted
# Exports the results to a csv file.
#
# Adjust the server list accordingly
# Adjust the path and file name for the output file to your needs
$results = @()
$Servers = "DC1", "FS1", "RPTS1", "SQL1"
$outputFile = ‘C:\Greg\boottimes.csv’
ForEach ($Computer in $Servers) {
$result = New-Object psObject
$result | Add-Member -MemberType NoteProperty -Name ‘Computer Name’ -Value $Computer
$rbttime = Get-RebootTime -ComputerName $Computer -Last | select -ExpandProperty RebootTime
$result | Add-Member -MemberType NoteProperty -Name ‘Last Reboot’ -Value $rbttime
$results += $result
}
$results | Export-Csv $outputFile -notype

 

SUCCESS!!  I had my csv file and was able to see all the servers and their reboot times.  This was a great exercise for me and it also gave me a nice little utility script that I could have handy or even schedule to run automatically so I always know how long its been between reboots of my servers. 

Read Full Post »

Using sFTP in SQL Server SSIS

On several occasions I have had to use SSIS to export data from a SQL database, and then transfer that data to a third party via sFTP.  SQL Server SSIS has an FTP task built in but it does not natively support sFTP.  There are several commercial products (Eldos Software, /n Software, CozyRoc) out on the market that can be used to add that functionality into SSIS but I am going to talk about using a free utility called WinSCP to accomplish this task.

The first step is to download and install WinSCP, you will need to do this on the SQL server that will be running the SSIS package.  Once you have installed WinSCP you should create a connection to the distant sFTP server in WinSCP so you can test and confirm that you can connect to it.  Create and name a saved session so that you can call that session from your script later.  I will name this session “MySecureFTP”.

image

Once you have done that you will need to create a WinSCP script to transfer your files.  For examples of scripts see the WinSCP documentation.  Here is a very basic script that I created to copy a csv file.

option batch on

option confirm off

open MySecureFTP

put c:\outbound\*.csv /inbounddirectory/

exit

After testing the script by running it from a command prompt and confirming that it works, you are ready to step into SSIS and call it from your workflow.

In Microsoft Visual Studio in your SSIS Package, go to the Control Flow tab and from your toolbox add an “Execute Process Task” to the workflow.  It should look like this after you’ve added it.

image

Double click the Execute Process Task to open the Task Editor.  You should now have a window like this.

image

Go to the Process section on the left and then you will need to fill out some information.

image

In the executable line you will need to fill out the correct path to the WinSCP executable.  Mine was in the (x86) directory but it may be different for your computer.

Then in the Arguments line you will need to put the /script= switch and then the path to that script.  I also included the /log= switch to log the execution to a file.  That way I can go back and review the FTP connection and make sure everything was a success. 

You may need to add an entry for the WorkingDirectory if you do not use absolute paths in your script.  I didn’t require it. 

 

Now you can build your SSIS package and deploy it.  That’s all that you need to do. 

 

One last comment that I’ve found is that if you’ve created a stored session in WinSCP it stores the value in the registry under the HKey\Current_User folder and depending on what credentials you use to run the SQL Job you may not have access to that registry key.  One option to fix this is to switch WinSCP to use an INI so all the stored sessions are stored in the INI.  That way the script can still access the stored session.  Another way to fix this is to set the SQL Job to run as a different user but that involves setting up a Proxy in SQL Server Agent which I will cover in another post.    

Read Full Post »

I was going through some old files and thought I would post this one as a good example of using VBScript.

I had a request to automate the upload of a file to an FTP site.  This example dates back to 2006 so I know there are better ways to do this now, but it is a good example of the flexibility of VB Scripting.

‘ VBScript source code

Dim Fso : Set Fso = CreateObject(“Scripting.FileSystemObject”)
Dim WshNetwork : Set WshNetwork = CreateObject(“Wscript.Network”)
Dim WshShell : Set WshShell = CreateObject(“WScript.Shell”)
Const LogFile = “C:\VBScript\FTP.log”
‘Declares the log file.
Set FSO = CreateObject(“Scripting.FileSystemObject”)
Dim strFileName : strFileName = “C:\VBScript\holdings.csv”

‘Checks to see if the file is ready.
If not Fso.FileExists(strFileName) then
‘File is not ready exit out and wait
Set WshShell = WScript.CreateObject(“WScript.Shell”)
RunString = “%comspec% /c echo ************************ “ & WeekDayName(WeekDay(Now), True) & ” “ & Now & ” FILE NOT READY ************************ “
Return = WshShell.Run(RunString & ” >> ” & LogFile & ” 2>&1″, 0, TRUE)
WScript.quit
Else

RunCmd

Sub RunCmd()
Set WshShell = WScript.CreateObject(“WScript.Shell”)
RunString = “%comspec% /c echo ********************************************** “
Return = WshShell.Run(RunString & ” >> “ & LogFile & ” 2>&1″, 0, TRUE)
RunString = “%comspec% /c echo “ & WeekDayName(WeekDay(Now), True) & ” ” & Now & “, “& strFileName & ” is available – sending via FTP”
Return = WshShell.Run(RunString & ” >> “ & LogFile & ” 2>&1″, 0, TRUE)
RunString = “%comspec% /c echo ********************************************** “
Return = WshShell.Run(RunString & ” >> “ & LogFile & ” 2>&1″, 0, TRUE)
‘FTP the file to client
RunString = “%comspec% /c %WINDIR%\System32\ftp.exe -i -n -s:C:\VBScript\instructions.txt “
Return = WshShell.Run(RunString & ” >> “ & LogFile & ” 2>&1″, 0, TRUE)
RunString = “%comspec% /c echo ********************************************** “
Return = WshShell.Run(RunString & ” >> “ & LogFile & ” 2>&1″, 0, TRUE)
RunString = “%comspec% /c echo FINISHED: “ & WeekDayName(WeekDay(Now), True) & ” “ & Now
Return = WshShell.Run(RunString & ” >> “ & LogFile & ” 2>&1″, 0, TRUE)
RunString = “%comspec% /c echo ********************************************** “
Return = WshShell.Run(RunString & ” >> “ & LogFile & ” 2>&1″, 0, TRUE)
Set WshShell = Nothing
End Sub
End

Basically this script will check to see if a file exists and if it does it will send the file via FTP to a remote location and log all the output to a log file.   This is a good simple script to quickly and repeatedly perform a task and log it.

Read Full Post »

Today I was setting up PowerShell 2010 for testing on a VM and I ran into an issue during the install that is apparently quite common. I had successfully installed the prerequisites and was attempting to install the actual product when I got the following error.

Setup is unable to proceed due to the following error(s):
A system restart from a previous installation or update is pending. Restart your computer and run setup to continue.
For the list of pre-requisites needed to install the product please refer to:
http://go.microsoft.com/fwlink/?LinkId=106209
Correct the issue(s) listed above and re-run setup.

After doing some research I found this link at Microsoft’s TechNet site about common installation issues. 

The fix for my dilemma was to find this registry key

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\PendingFileRenameOperations

To delete the orphaned PendingFileRenameOperations registry key  

  1. Open a registry editor, such as Regedit.exe or Regedt32.exe.
  2. Navigate to HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\
  3. In the right navigation pane, right-click the PendingFileRenameOperations key and select Delete.
  4. Close Registry Editor.

After deleting the registry key (MAKE A BACKUP JUST IN CASE) I was able to continue on and complete my installation of SharePoint 2010.

Read Full Post »