Import lot of large .CSV files to remote MSSQL database asyncronously via Powershell script

Question

I'm trying to upload tons of data stored in .csv files to remote MSSQL.

I found working solution (at least for syncronous mode) here.

I'm trying to start part with asyncronous mode via Powershell jobs, but it's failing.

Here is code:

Get-ChildItem "G:\Data\*.csv" | % {

Start-job -Name "$($_)" -InitializationScript  {Ipmo Functions -Force -DisableNameChecking} `
-ScriptBlock { $DataImport = Import-Csv -Path $args[0]
               $DataTable = Out-DataTable -InputObject $DataImport
               Write-DataTable -ServerInstance "MSSQL" `
                              -Database "database" `
                             -TableName "table" `
                                -Username "user" `
                              -Password "pwd" `
                              -Data $DataTable
 } -ArgumentList $_.fullname}

I receive error:

Ipmo : The specified module 'Functions' was not loaded because no valid module file was found in any module directory. At line:1 char:1 + Ipmo Functions -Force -DisableNameChecking + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : ResourceUnavailable: (Functions:String) [Import-Module], FileNotFoundException + FullyQualifiedErrorId : Modules_ModuleNotFound,Microsoft.PowerShell.Commands.ImportModuleCommand

Could anyone please help?

How do you install your modules? Start-Job launches a new process and I'm not certain what profile it uses. It seems from the error that it doesn't know where your custom Functions module is. If your modules path is loaded through your profile, that might be one place to look. — beavel

Keith Hill Keith Hill · Accepted Answer · 2014-02-22T19:15:16

Processing these files using Start-Job in this manner without throttling the number of jobs you're creating might not be very performant. That is if there are 1000's of CSV files, you're going to create 1000's of jobs. If you are on PowerShell V3 or higher you might try workflow to get a more "managed" parallel execution:

workflow Process-CsvFile([string[]]$Files) {
    InlineScript { Import-Module Functions -DisableNameCheck }
    foreach -parallel($file in $Files) {
        InlineScript {
            $DataImport = Import-Csv -Path $args[0]
            $DataTable = Out-DataTable -InputObject $DataImport
            Write-DataTable -ServerInstance "MSSQL" `
                            -Database "database" `
                            -TableName "table" `
                            -Username "user" `
                            -Password "pwd" `
                            -Data $DataTable
        } 
    }
}

$files = Get-ChildItem G:\Data\*.csv| Foreach FullName
Process-CsvFile $files

Regarding your issue with the error on importing Functions. The Out-DataTable and Write-DataTable functions need to be copied into a file called Functions.psm1 and that file should be put in one of two places - either $home\Documents\WindowsPowerShell\Modules\Functions\Functions.psm1 or $pshome\Modules\Functions\Functions.psm1. I recommend putting it in the first location, under your home dir. After putting the file in the proper location, open PowerShell and execute ipmo Functions -DisableNameChecking to verify you get no errors.

If you don't want parallel execution then you need to modify how your are using Start-Job because these jobs do run in parallel. Create a single job to run the CSV files sequentially but in the background e.g.:

$files = Get-ChildItem G:\Data\*.csv| Foreach FullName
Start-job -Name ProcessCsvFiles`
          -InitializationScript  {Ipmo Functions -Force -DisableNameChecking} `
          -ScriptBlock {param($files) 
               foreach ($file in $files) {
                   $DataImport = Import-Csv -Path $file
                   $DataTable = Out-DataTable -InputObject $DataImport
                   Write-DataTable -ServerInstance "MSSQL" `
                                   -Database "database" `
                                   -TableName "table" `
                                   -Username "user" `
                                   -Password "pwd" `
                                   -Data $DataTable
               }
           } -ArgumentList $files

Import lot of large .CSV files to remote MSSQL database asyncronously via Powershell script

1 Answers