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
– beavel, Commented Feb 22, 2014 at 14:05

Braiam · Accepted Answer · 2020-12-30 15:49:03Z

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

I actually don't need parallel. Much better if files will be processed one by one.
Then I would not start a new job for every file then. Just process all the files within a single job.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related