BULK INSERT fails

Nov 23, 2011 at 5:56 PM

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
(
formatfile='\\2G-030c\c$\HHC_Peoplesoft\EmployeeContactInfo.fmt'
)

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.

Nov 23, 2011 at 6:29 PM

FYI,

2G-030c was my PC.

mhpsqlins09\mhpdevsrv01 is my test SQL Server

If I move the text file and the format file to the same box as SQL Server, BULK INSERT works. It just won't work when text files are in my pc. It must be a security issue. So I gave full control for the folder, but I still get the error mentioned above. Why ?

Coordinator
Nov 23, 2011 at 7:24 PM
jimmycjen wrote:

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
(
formatfile='\\2G-030c\c$\HHC_Peoplesoft\EmployeeContactInfo.fmt'
)

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:

Write-host $query

Nov 23, 2011 at 8:56 PM

Thanks for getting back to me.

So, I change the code to as follow:

$filename = "\\2G-030C\C$\HHC_Peoplesoft\HHC_COMPANY_PROPERTY_2011-11-18-21-00-27.txt"

$query = "bulk insert EmployeeContactInfo from '" + $filename + "' with (formatfile='\\2G-030C\C$\textfile\EmployeeContactInfo.fmt')"

Set-SqlData -sqlserver  "mhpsqlins09\mhpdevsrv01" -dbname "JobInfo" -qry $query

This still fails.

When I use write-host $query, it shows:

bulk insert EmployeeContactInfo from '\\2G-030C\C$\HHC_Peoplesoft\HHC_COMPANY_PROPERTY_2011-11-18-21-00-27.txt' with (formatfile='\\2G-030C\C$\HHC_Peoplesoft\EmployeeContactInfo.fmt')

According to SQL Server BOL, BULK INSERT won't work in my situation using Windows Authentication. In my situation, it suggests SQL Authentication, and it worked.

BTW, is there cmdlet for BULKINSERT ?

Coordinator
Nov 23, 2011 at 9:38 PM

I've hit that issues also. One of the limitations of BULK INSERT

The functions/cmdlets we have are mostly built on top of SMO, RMO, or ADO.NET. The SMO classes don't expose BULKINSERT and I'm not sure we would do much more than a very thin wrapper around the T-SQL equivalent without adding much value.

BTW -- There's a Scripting Guy article which will be published Monday, Nov. 28th 2011 which will demonstrate three different methods using BULK INSERT, LogParser and SqlBulkCopy of loading a CSV into SQL Server from Powershell:

http://blogs.technet.com/b/heyscriptingguy/