PowerShell 腳本將數據從 FTP 伺服器載入到 SQL

我有一個 power shell 腳本，可以將數據從 ftp 位置載入到 sql Server 中。該腳本目前僅載入昨天的文件。如何讓它查找最近 7 天的文件？

這是我的腳本

. "$PSScriptRoot\Send-MailMessage.ps1"
. "$PSScriptRoot\DB.ps1"

if ($sqlserver -eq $null)
{
   throw 'Missing database configuration!'
} 
$database = 'XXXX' 
$table = 'dbo.Stage_Data'
$csvDir = "$PSScriptRoot\ResRefNums"
$csvdelimiter = ','
$firstRowCsv = 'Column_1,Column_2,Column_3,Column_4,Column_5,Column_6,Column_7'
$firstRowPipe = 'Column_1|Column_2|Column_3|Column_4|Column_5|Column_6|Column_7'
$databaseCols = 'Column_1,Column_2,Column_3,Column_4,Column_5,Column_6,Column_7'

try
{
Set-Location $PSScriptRoot
$date =  $((Get-Date).AddDays(-1).ToString('dd.MM.yyyy'));

$FileToProcess = "DataFeed_" + $date + ".csv"
if (!(Test-Path "abcd"))
{
   write-output "Remote Dir not found"
}
Copy-Item -Path  "abcd" -Destination $csvDir
$totalRows = 0

if (!(Test-Path $csvDir)){
   write-output "Creating $csvDir"
   mkdir $csvDir    
}
#  $files = Get-ChildItem -file "$csvDir\\*.csv"
$files = Get-ChildItem -file "$csvDir\\$FileToProcess"
for ($i=0; $i -lt $files.Count; $i++)
{
   # CSV variables;  
   $csvfile = $files[$i].FullName
   if (Test-Path "$csvfile.txt")
   {
       write-output "Already imported, skipping: $csvfile."
       continue
   }
   if (Test-Path "$csvfile.err")
   {
       write-output "Previous error, skipping: $csvfile."
       continue
   }

   $rows = 0 
   #get list of csv files to process
   If (!(Test-Path $csvfile)){
       write-output "Cannot find $csvfile"
       continue
   }
   $batchsize = 50000 

   # Build the sqlbulkcopy connection, and set the timeout to infinite 
   $connectionstring = "Data Source=$sqlserver;Integrated Security=true;Initial Catalog=$database;" 
   $bulkcopy = new-object ("Data.SqlClient.Sqlbulkcopy") $connectionstring 
   $bulkcopy.DestinationTableName = $table 
   $bulkcopy.bulkcopyTimeout = 60 
   $bulkcopy.batchsize = $batchsize 
   $bulkcopy.EnableStreaming = 1 

   # Create the datatable, and autogenerate the columns. 
   $datatable = New-Object "System.Data.DataTable" 

   # Open the text file from disk 
   $reader = new-object System.IO.StreamReader($csvfile)
   $line = $reader.ReadLine()
   $reader.Close()
   #csv headers
   $header = @()
   if ($line -eq $firstRowCsv)
   {
       $date = get-date
       $outmsg = $date.ToString() + ":" + "Detected commas in first row of $csvfile"
       Write-Output $outmsg
       $header = $firstRowCsv.Split($csvdelimiter) 
   }
   elseif ($line -eq $firstRowPipe)
   {
       $date = get-date
       $outmsg = $date.ToString() + ":" + "Detected pipes in first row of $csvfile"
       Write-Output $outmsg 
       $csvdelimiter = "|"
       $header = $firstRowPipe.Split($csvdelimiter)
   }
   else
   {
      # $date = get-date
       $outmsg = $date.ToString() + ":" + "First row does not match expected columns, skipping $csvfile"
       Write-Output $outmsg 
       $outmsg | Out-File "$csvfile.err"

       $params = @{ 
           Body = $outmsg 

          Attachments = @($csvfile)
           Priority = 'High' 
       }

       # send email fail, do not fail job
       Send-MailMessage @params
       Write-Output "Sent failure email."
   }

   $dbCols = $databaseCols.Split(',')

   $null = $datatable.Columns.AddRange($dbCols)
   $rownum = 1; #add 1 for column headers
   # Match database column order!!!
   Import-CSV -Delimiter $csvdelimiter $csvfile | Foreach-Object{
       $row = $datatable.NewRow()
       $rownum++;
       for ($c=0; $c -lt $header.Count; $c++)
       {
           $row.$($dbCols[$c]) = $_.$($header[$c])
       }
       $datatable.Rows.Add($row)
   }

   $bulkcopy.WriteToServer($datatable)

 #  $date = get-date
   $outmsg = $date.ToString() + ":" + $datatable.Rows.Count.ToString() + " rows have been inserted into the database from " + $csvfile
   $datatable.Clear()
$updateQuery = "UPDATE " + $table + " SET Source = 'B'
 WHERE DNIS IS NULL 
 AND LEN(Source) <> 1"

if ($updateQuery.Length -gt 0)
{
   Write-Output "Updating SentToVIQFlag in databasename.dbo.tabelname..."
   # $connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
   # $connectionString = [string]::Format($connectionTemplate, $server, $database)
   $connection = New-Object System.Data.SqlClient.SqlConnection
   $connection.ConnectionString = $connectionString
   $connection.Open()

   $command = New-Object System.Data.SqlClient.SqlCommand
   $command.connection = $connection
   $command.CommandTimeout = 600000
   $command.CommandText = $updateQuery
   $rowsAffected = $command.ExecuteNonQuery()
   $connection.Close()

   Write-Output "Database updated ($rowsAffected rows)."
}

   $outmsg | Out-File "$csvfile.txt"

   Write-Output $outmsg
}
Write-Output "Script Complete"
exit 0;
}
catch [Exception]
{
   Write-Output $_.Exception.Message
   exit 1;
}

您的腳本只嘗試處理單個日期，您不會生成要處理的日期列表。您只需將 FTP 呼叫包裝在 for 循環中即可解決此問題：

# always cache the root date as multiple calls to Get-Date could cross day boundaries...
$startdate = Get-Date

for($offset = -7; $offset -lt 0; $offset++)
{
   $date =  $($startDate.AddDays($offset).ToString('dd.MM.yyyy'));

   $FileToProcess = "DataFeed_" + $date + ".csv"

   # the rest of your script...
}

引用自：https://dba.stackexchange.com/questions/188438