Search code examples
sql-serverbcp

"BCP copy in failed" on TSV

Note: this is very similar to the question I posted here ("BCP copy in failed" on tsv (no further error message info)), but I resolved that question with a different solution that does not appear to work in this case. (Title of question similar so that anyone else having these same vague errors can find multiple potential solutions to their problem).

Trying to use BCP utility (https://learn.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-2017) on linux CentOS7 to copy tsv files into remote MSSQL Server DB (in a Windows Server 2012 machine), getting the frustratingly curt error message that

Starting copy...

BCP copy in failed

The BCP bash script in question has the form

TO_SERVER_ODBCDSN="-D -S MyMSSQLServer"
TO_SERVER_IP="-S 172.99.9.29"
DB="mydb"
TABLE="mytable" 
# getting MSSQL Server credentials
USER=$(tail -n+1 $basedir/src/mssql-creds.txt | head -1)
PASSWORD=$(tail -n+2 $basedir/src/mssql-creds.txt | head -1)
DATAFILES="$storagedir/tsv"
TARGET_GLOB="*.tsv"
RECOMMEDED_IMPORT_MODE='-c' # see https://stackoverflow.com/a/16310219/8236733
DELIMITER="\t" # DO NOT use format like "'\t'", nested quotes seem to cause hard-to-catch error

if [[ -z "${TABLE// }" ]]; then
    echo -e "\nNo table specified"
    exit
fi

{
echo -e "Truncating destination table: $DB/$TABLE"
sqlcmd -Q "select count(*) from dbo.$TABLE; truncate table dbo.$TABLE; select count(*) from dbo.$TABLE;" \
    $TO_SERVER_ODBCDSN \
    -U $USER -P $PASSWORD \
    -d $DB
} || { echo -e "\nFailed to truncate MSSQL DB"; exit 255; }

echo -e "\nConnecting with BCP utility as $USER..."

for filename in $DATAFILES/$TARGET_GLOB; do

    if [ ! -f $filename ]; then
        echo -e "\nFile $filename not found!"
    else
        echo -e "\nImporting $filename data to $DB/$TABLE"
    fi

    echo -e "Removing header from TSV file $filename"
    echo "$(tail -n +2 $filename)" > $filename

    echo -e "Replacing null literal values with empty chars"
    NULL_WITH_TAB="null\t" # WARN: assumes the first field is prime-key so never null
    TAB="\t"
    sed -i -e "s/$NULL_WITH_TAB/$TAB/g" $filename

    echo -e "Starting BCP export threads for $filename"
    /opt/mssql-tools/bin/bcp "$TABLE" in "$filename" \
        $TO_SERVER_ODBCDSN \
        -U $USER -P $PASSWORD \
        -d $DB \
        $RECOMMEDED_IMPORT_MODE \
        -t "\t" \
        &

done

(providing more than what may be a minimal example here, since I have zero idea why the BCP program is failing on only certain datasets and working on others, being that the error message is so vague) and running this produces the output

[me@mapr001 src]$ time ./hdfs2mssql.pq.sh mytable

Truncating destination table: mydb/mytable

-----------
          0

(1 rows affected)

-----------
          0

(1 rows affected)

Connecting with BCP utility as myuser...

Importing /mapr/my.cluster.local//etl/path/to/MYTABLE/tsv/0_0_0.tsv data to mydb/mytable
Removing header from TSV file /mapr/uceramapr.cluster.local//etl/path/to/MYTABLE/tsv/0_0_0.tsv
Replacing null literal values with empty chars
Starting BCP export threads for /mapr/uceramapr.cluster.local//etl/path/to/MYTABLE/tsv/0_0_0.tsv

Starting copy...

BCP copy in failed


All export tasks collected and completed

real    0m2.182s
user    0m0.826s
sys 0m0.624s

From this, it tells some debugging info.

  1. It tells me that sqlcmd is able to actually connect to the remote DB (since able to successfully truncate and get count), ie. unlikely to be a network connection issue.
  2. Also tells that the problem is (likely) not the same as I had solved in another SO post about BCP being unable to do "in" operation for a TSV file ("BCP copy in failed" on tsv (no further error message info)), since am already replacing the literal 'null' strings with tabs "\t".

Attempting to inspect the number of tabs for each row in the TSV file via awk '{print gsub(/\t/,"")}' /path/to/0_0_0.tsv appear to show all rows as having 149 tabs. This seems to match up with the 149+1=150 columns in the schema of the remote DB destination table (schema via sp_help <tablename> shown below, sorry if badly formatted):

Column_name Type    Computed    Length  Prec    Scale   Nullable    TrimTrailingBlanks  FixedLenNullInSource    Collation
PROC_ID float   no  8   53      NULL    yes (n/a)   (n/a)   NULL
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    float   no  8   53      NULL    yes (n/a)   (n/a)   NULL
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    smalldatetime   no  4                   yes (n/a)   (n/a)   NULL
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    nvarchar    no  510                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
xxxx    smalldatetime   no  4                   yes (n/a)   (n/a)   NULL

And here's a mock sample row from the tsv

272647  KXXXX,XXXXSR TRNS>10KILO    31999999                                        19999               57              Y   2   9/1/17 XXX,NO VOL X XXX (XXX XXX XXXX)              Y   999                                             1999    2999                            999         3                       1   1                           Y               2012-05-31 17:41:00         1   KXX,PRXXXX T                                                                                    999                                                                                                     XXXT,PRXXXX TXXX>10KILO                                                                                                                     2018-08-21 14:04:57

here with the tab escape characters

272647^IKXXXX,XXXXSR TRNS>10KILO^I31999999^I^I^I^I^I^I^I^I^I^I19999^I^I^I^I57^I^I^I^IY^I2^I9/1/17 XXX,NO VOL X XXX (XXX XXX XXXX)^I^I^I^IY^I999^I^I^I^I^I^I^I^I^I^I^I^I1999^I2999^I^I^I^I^I^I^I999^I^I^I3^I^I^I^I^I^I1^I1^I^I^I^I^I^I^IY^I^I^I^I2012-05-31 17:41:00^I^I^I1^IKXX,PRXXXX T^I^I^I^I^I^I^I^I^I^I^I^I^I^I^I^I^I^I^I^I^I999^I^I^I^I^I^I^I^I^I^I^I^I^I^I^I^I^I^I^I^I^I^I^I^I^I^IXXXT,PRXXXX TXXX>10KILO^I^I^I^I^I^I^I^I^I^I^I^I^I^I^I^I^I^I^I^I^I^I^I^I^I^I^I^I^I^I2018-08-21 14:04:57

In terms of providing a minimal working example, the code from the script would be 

#!/bin/bash

filename=$1

# can use ODBC driver...
TO_SERVER_ODBCDSN="-D -S MyMSSQLServer"
# ...or host IP
TO_SERVER_IP="-S <your MSSQL Server IP>"
DB="mydb"
TABLE="mytable"
USER=<your MSSQL Server login>
PASSWORD=<your MSSQL Server login password>
DATAFILES=</path/to/tsv/file(s)>
TARGET_GLOB="*.tsv"
RECOMMEDED_IMPORT_MODE='-c' # see https://stackoverflow.com/a/16310219/8236733
DELIMITER="\t" # DO NOT use format like "'\t'", nested quotes seem to cause hard-to-catch error

echo -e "Removing header from TSV file $filename"
echo "$(tail -n +2 $filename)" > $filename
echo "First line of file is now..."    
echo $(head -n 1 $filename)

echo -e "Replacing null literal values with empty chars"
NULL_WITH_TAB="null\t" # WARN: assumes the first field is prime-key so never null
TAB="\t"
sed -i -e "s/$NULL_WITH_TAB/$TAB/g" $filename

echo -e "Starting BCP export threads for $filename"
/opt/mssql-tools/bin/bcp "$TABLE" in "$filename" \
    $TO_SERVER_ODBCDSN \
    -U $USER -P $PASSWORD \
    -d $DB \
    $RECOMMEDED_IMPORT_MODE \
    -t "\t" \

(where some values must be provided by user, since depends on how systems have setup MSSQL Server and ODBC driver, etc.). However, cannot provide example data for this, since the data I am currently working with is sensitive and I don't know what about the datasets is causing the error (as I said, the script appears to work for some datasets and not for others) in order to produce some mock dataset online that would generate a similar error.

Trying this command with a different table and data-file does work and at this point the error message is too brief to give me more to debug on (never used BCP utility before). If anyone has experience using this tool, any debugging advice or how to solve this problem would be appreciated.

Solution

  • Finally found solution and appears to have little to do with BCP (though leaving question title as is, since BCP is where the problem came to light and so may be how others may likely find this post).

    TLDR:

    Datetime values in the TSV were overflowing the destination field in the destination table because that table schema was expecting a smalldate type, but at the source end we were treating the data as if we thought that the destination could handle a datetime type in that particular column.

    Moral of the story:

    When debugging ETL / data transfer issues make sure to check if any questionable data type conflicts between source and destination may be involved.

    Long version:

    Using a binarysearch debugging method on the actual dataset files that are causing the problems (eg. using the first half of the data, see what happens, half that, try again, etc.), was able to avoid the "BCP copy failed" error, but was then seeing that (even though no failure message thrown, 0 rows were being copied (so I really don't know why the "BCP copy failed" errors no longer popped up)). Adding the -e option to the minimal example to get an error log of the bcp copy attempt here (eg. -e filename.bcperror.log), saw the error

    #@ Row 1, Column 150: Datetime field overflow. Fractional second precision exceeds the scale specified in the parameter binding. @#

    at the top of every row of the generated error file (column 150 being the last column in a row, eg. 2018-08-29 11:34:14).

    Looking at the table that BCP is trying to copy to in MSSQL Server, I noticed that (unlike in the tables that BCP was successfully writing to), the final field (column 150) in my case was set as smalldate type whereas other tables used datetime. Changing this field to datetime as well, BCP was able to copy the problem TSVs to the tables without issue.