0

I have a SSIS Execute Process Task which calls a .bat file which calls SQL Server BCP IN to move data from a file to SQL Server.

Now even if BCP IN fails to transfer some records (due to data type mismatch) Execute Process Task does not fail. We want Execute process task to fail in this condition. How can this be done?

When we run BCP IN directly on command prompt we get below kind of messages for records which BCP IN fails to transfer.

SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]String data, right truncation

We tried to use %ERRORLEVEL% but it remains set to 0.

Pritesh
  • 1,938
  • 7
  • 32
  • 46
  • 1
    BCP sets DOS ERRORLEVEL unpon failure in most circumstances. See here for an example of how to identify and propagate this. http://bytes.com/topic/sql-server/answers/82815-bcp-within-batch-file – Nick.Mc Apr 16 '15 at 11:49
  • %ERRORLEVEL% is set if BCP command has some probelm (say wrong connection string) but it is not set when some records are not loaded. – Pritesh Apr 20 '15 at 08:35
  • This link https://groups.google.com/forum/#!topic/microsoft.public.sqlserver.tools/qzpWuZzJnr4 says BCP v9.0 sets the errorlevel correctly but v8.0 does not. Do you know which version you have? – Nick.Mc Apr 20 '15 at 10:46
  • I am using version 10.50.2811.0 – Pritesh Apr 20 '15 at 11:05
  • So just to clarify (perhaps you could add to the original question), a truncation error leaves ERRORLEVEL at 0 - is that correct? I'm sorry I can't help further. Looks like there is a workaround if you send the output to a loag and check the log afterwards though. – Nick.Mc Apr 20 '15 at 11:39

0 Answers0