How to get build to fail when running bcp from an msbuild script?
As part of sql server 2005, there is the bulk copy command "bcp.exe" (http://msdn.microsoft.com/en-us/library/ms162802%28SQL.90%29.aspx)
We are running the bcp command from our msbuild script using the exec task. Unfortunately, when bcp fails to load a row of data, the build still succeeds.
I tried (per build script snippet below) specifying an error file, and checking for its existence, unfortunately that means the build always fails even if bcp completely succeeds, because it appears that bcp will always create the error file even if there are no errors.
<ItemGroup> <bcpFiles Include="$(DataPath)\*.txt" /> </ItemGroup> <Delete Files="BcpErrors.txt" /> <Message Text="bcp $(DatabaseName).dbo.%(bcpFiles.FileName) in %(bcpFiles.FullPath) -eBcpErrors.txt -c -E -q -t"`" -r"`\n" $(bcpConnectionString)" /> <Exec Command="bcp $(DatabaseName).dbo.%(bcpFiles.FileName) in %(bcpFiles.FullPath) -eBcpErrors.txt -c -E -q -t"`" -r"`\n" $(bcpConnectionString)" /> <Error Condition="Exists('BcpErrors.txt')" Text="One or more bcp commands contained errors." />
Is there any way I can get msbuild to fail the build if bcp fails to load any of the data?
If BCP returns a non-zero error code for failure then the MSBuild Exec task should detect that and mark the task as failed.
You could use the MSBuild ExtensionPack "File" task to look for error text in the output file:
<MSBuild.ExtensionPack.FileSystem.File TaskAction="FilterByContent" RegexPattern="Error = " Files="BcpErrors.txt"> <Output TaskParameter="IncludedFileCount" PropertyName="ErrorFileCount"/> </MSBuild.ExtensionPack.FileSystem.File> <Error Condition="$(ErrorFileCount) != 0" Text="One or more bcp commands contained errors." />
Ok, so it's not exactly using bcp.exe, but BULK INSERT is very similar to bcp.exe, can take the same format of files, and has most of the same options. The Sql.Execute task will also fail if the BulkInsert query fails. So I ended up with
<ItemGroup> <bcpFiles Include="$(DataPath)\*.txt" /> </ItemGroup> <Sql.Execute ConnectionString="$(ConnectionString)" Sql="BULK INSERT [$(DatabaseName)].dbo.[%(bcpFiles.FileName)] FROM '%(bcpFiles.FullPath)' WITH (DATAFILETYPE='char', KEEPIDENTITY, FIELDTERMINATOR='`', ROWTERMINATOR='`\n', MAXERRORS=1)" />
(Sql.Execute task is defined in Microsoft.Sdc.Common.tasks)