We have a request to count the daily number of files processed by our FTP server. To do this, we setup a new SQL Server and add it to the FTP software to store all the data that we need. I was going to use PowerShell to do the whole thing until I saw the SQL Server Agent Jobs that allows us to run a PowerShell script. I do not see why we have to create a separate schedule tasks to run the PowerShell when we can do everything from within SQL Server. So, off we go with setting up a recurring job that count the file processed daily by our FTP server.
To do that, I already wrote a SQL command that will query the customized report from the database. I saved this query as FileCount.sql and placed it in C:\scripts of the SQL Server.
- Using SSMS, connect to the SQL Server and go to SQL Server Agent -> jobs -> right click and select “New Job”
- Give the job a name. Mine is “FileCountReport”. Put in a Description so your colleagues will know what this Job is for.
- Go to Steps and create a new step.
- Give the Step a name, change “Type” to PowerShell. Then, select the SQL Server Agent Service Account (or a Service Account that you can use) for the job to run as. The Command window will hold the PowerShell script for this job.
- Below is the PowerShell Script that I used for this job. You will need to modify the variables if you wish to reuse it. This script will query the database with the query I saved in the .sql file, process it with the PowerShell script and then send an email out using the mail-relay server that we have. The part that counts the file will probably not work for you, as this works only with the output I got from the SQL query. If you do not have a mail relay server, you will need to provide credential to your account before you can send the email. Follow my other post here and see how I automated to send monthly email with a Gmail account.
#Declare Variables
$date = (get-date).AddDays(-1).ToString("MM-dd-yyyy")
#replace the smtpserver (my mail-relay server) and the from, to, cc, bcc email address here (remove cc and bcc if there is none)
$SmtpServer = "mail-relay.kennyl.us"
$From = "aleart@kennyl.us"
$To = "kenny@kennyl.us"
$CC = "kenny@kennyl.us"
$BCC = "kenny@kennyl.us"
$Subject = "Type in your subject here"
$Body = "Type in your body here"
#replace the path to the attachment below
$Attachment = "C:\temp\FileCount\$date.txt"
#Invoke Sqlcmd to create the report with CSV. Replace databasename and file path to your own.
Invoke-Sqlcmd -Database databasename -InputFile "C:\Scripts\FileCount.sql" | Export-CSV "C:\temp\FileCount\$date.csv"
#Creating a new .txt file and count the file processed
New-Item C:\temp\FileCount\$date.txt
$files = Import-CSV C:\temp\FileCount\$date.csv
$Counts = $files.name |split-path -parent | Group-Object -noelement
ForEach ($Count in $Counts){
$name = $count.name
$n = $count.count
Add-content -path c:\temp\fileCount\$date.txt -value "$date $name = $n"
}
#Attach the .txt file and send it through mail-relay, remove Cc and Bcc if you are not using it. Otherwise, you will get an error.
$mailParams = @{
SmtpServer = $SmtpServer
port= 25
From = $From
To = $To
Cc = $CC
Bcc = $Bcc
Subject = $Subject
Body = $Body
attachment = $Attachment
DeliveryNotificationOption = 'Never'
}
Send-MailMessage @mailParams
#Remove .csv and .txt from C:\temp\FileCount to clean up after ourselves.
GET-CHILDITEM -PATH "C:\temp\filecount" | REMOVE-ITEM -RECURSE -FORCE
- Next, go to “Schedules” to setup a recurring schedule.
- Give the schedule a name and change the recurring schedule according to your need.
After saving the SQL Server Agent Job, you will be able to right click on the Job and “Start Job at Step” to test if everything is working as intended.
KennyL System Admin 