when I run BULK INSERT from Management Studio it works. Below is the SQL statement:
BULK INSERT EmployeeContactInfo from 'c:\HHC_Peoplesoft\HHC_COMPANY_PROPERTY_2011-11-18-21-00-27.txt' with
But when I execute this from Powershell it fails:
$file = Get-ChildItem "c:\HHC_Peoplesoft\HHC_COMPANY_PROPERTY_2011-11-18-21-00-27.txt"
$query = "bulk insert EmployeeContactInfo from '" + $file + "' with (formatfile='c:\HHC_Peoplesoft\EmployeeContactInfo.fmt')"
Set-SqlData -sqlserver "mhpsqlins09\mhpdevsrv01" -dbname "JobInfo" -qry $query
It fails with following error:
Exception calling "ExecuteNonQuery" with "1" argument(s): "ExecuteNonQuery failed for Database 'JobInfo'. "
At C:\Documents and Settings\JENJ\My Documents\WindowsPowerShell\Modules\SQLPSX\Modules\SQLServer\SQLServer.psm1:line:259 char:30
+ $database.ExecuteNonQuery( <<<< "$qry")
Can anyone help me why it's not working in Powershell ?
Thanks in advance.
The problem is you're forgetting that Powershell emits objects and not strings. Your call to $file = Get-ChildItem "c:\HHC_Peoplesoft\HHC_COMPANY_PROPERTY_2011-11-18-21-00-27.txt" returns an object of type System.IO.FileInfo. You can see this by
looking at get-member:
$file | get-member
An object has properties and methods and isn't simple string as you were expecting. So two ways to fix this:
Remove the call to get-childitem:
$file = "c:\HHC_Peoplesoft\HHC_COMPANY_PROPERTY_2011-11-18-21-00-27.txt"
Access the FullName property of your $filename object:
$file = $($file.FullName)
As a troubleshooting step I would suggest verifying $query is what you expect before calling code: