PowerShell 2.0 - Capture file names

Powershell 2.0 is a very powerful tool  and certainly can be used for sql server database tasks. One of the rquirement i had was to get the list of filesnames and the lastmodified date in a particular drive/folder, use the most recent file as a excel file source to one of my SSIS packages. One of the approaches as mentioned here was to create a table which will contain the filename and the last modified date. In this case the all the files from different days have the same schema/layout. One of the first thins i did was use a powershell script to look for the file with the given extension and store each of the file and date into the sql server table. I would like to thank Sean McCown for showing how to use the dir command in powershell to find files, described in his blog(http://midnightdba.itbookworm.com/). Here is the script which finds the files, then I loop through the collection and insert records into the sql server table. I developed this in Windows Power ISE editor version 2.0, In this script i look for excel spreadsheets.

add-pssnapin SqlServerCmdletSnapin100

set-location "c:\ssis\"

$query=“INSERT INTO Table_1 SELECT "

$files=dir -recurse -include *.xls

foreach ($file in $files)

{

     $file.name,$file.LastWriteTime

     $fquery = $query + "'"+$file.name+"'"+", "+"'"+$file.LastWriteTime+"'"

     invoke-sqlcmd -query $fquery -database MyDb -serverinstance MyServer
}
 
The above script inserts records into the Table_1 table in the database MyDb. Once this is done in the job step, in the next step I run a query to find the most recent file and use that as the file as an excel source in the SSIS package.