BCP import and column name starting with number fails

[isaacrlee]

Issue Description

• Description of the issue: When trying to import data from a table (Snowflake in my case) with a column that has a name starting with a number (e.g. "123col") to SQL Server, the replication fails if use_bulk is set to true. When use_bulk is set to false, the replication succeeds. In the logs, I see there is some logic to handle columns that are named like that as I'm seeing both the column name with a _ prefix and without in the created temp table e.g. create table "test"."test_int_col_sling_tmp" ("_123col" bigint, "123col" bigint). It feels like this has unhandled consequences for the BCP flow. If there's anything I can do to help with investigation for this admittedly very specific edge case, please let me know.

• Sling version (sling --version): 1.4.3

• Operating System (linux, mac, windows): Windows

• Replication Configuration:

replication_config = {
    "source": "SNOWFLAKE_SOURCE",
    "target": "SQL_SERVER_SOURCE",
    "streams": {
        "research_dev.test_int_col_sling": {
            "object": "test.test_int_col_sling",
            "mode": "full-refresh",
        }
    }
}

• Log Output (please run command with -d):

�[90m2025-02-18 16:10:02�[0m �[33mDBG�[0m Sling version: 1.4.3 (windows amd64)
�[90m2025-02-18 16:10:02�[0m �[33mDBG�[0m type is db-db
�[90m2025-02-18 16:10:02�[0m �[33mDBG�[0m using: {"columns":null,"mode":"full-refresh","select":null,"transforms":null}
�[90m2025-02-18 16:10:02�[0m �[33mDBG�[0m using source options: {"empty_as_null":false,"null_if":"NULL","datetime_format":"AUTO","max_decimals":-1}
�[90m2025-02-18 16:10:02�[0m �[33mDBG�[0m using target options: {"datetime_format":"auto","file_max_rows":0,"max_decimals":-1,"use_bulk":true,"add_new_columns":true,"adjust_column_type":false,"column_casing":"source"}
�[90m2025-02-18 16:10:02�[0m �[32mINF�[0m connecting to source database (snowflake)
�[90m2025-02-18 16:10:03�[0m �[33mDBG�[0m opened "snowflake" connection (conn-snowflake-lYC)
�[90m2025-02-18 16:10:04�[0m �[32mINF�[0m connecting to target database (sqlserver)
�[90m2025-02-18 16:10:06�[0m �[33mDBG�[0m opened "sqlserver" connection (conn-sqlserver-0lV)
�[90m2025-02-18 16:10:06�[0m �[32mINF�[0m reading from source database
�[90m2025-02-18 16:10:07�[0m �[33mDBG�[0m �[36mCREATE STAGE IF NOT EXISTS "RESEARCH_DEV"."SLING_STAGING"�[0m
�[90m2025-02-18 16:10:07�[0m �[33mDBG�[0m �[36mREMOVE @"RESEARCH_DEV"."SLING_STAGING"/sling_temp/1739913007764�[0m
�[90m2025-02-18 16:10:08�[0m �[33mDBG�[0m �[36mCOPY INTO '@"RESEARCH_DEV"."SLING_STAGING"/sling_temp/1739913007764/01_'
from (select * from "RESEARCH_DEV"."TEST_INT_COL_SLING")
FILE_FORMAT = (
TYPE = CSV
RECORD_DELIMITER = '\n'
NULL_IF = '\\N'
COMPRESSION = GZIP
ESCAPE_UNENCLOSED_FIELD = NONE
FIELD_OPTIONALLY_ENCLOSED_BY = '0x22'
)
HEADER = TRUE�[0m
�[90m2025-02-18 16:10:09�[0m �[33mDBG�[0m Unloaded 1 rows to @"RESEARCH_DEV"."SLING_STAGING"/sling_temp/1739913007764
�[90m2025-02-18 16:10:09�[0m �[33mDBG�[0m �[36mLIST @"RESEARCH_DEV"."SLING_STAGING"/sling_temp/1739913007764�[0m
�[90m2025-02-18 16:10:09�[0m �[33mDBG�[0m �[36mGET @"RESEARCH_DEV"."SLING_STAGING"/sling_temp/1739913007764 'file://C:/Users/ilee/AppData/Local/Temp/snowflake/get/2025-02-18T161007.764' overwrite=true parallel=8�[0m
�[90m2025-02-18 16:10:11�[0m �[33mDBG�[0m
+--------------------------------+------+------------+---------+
| FILE                           | SIZE | STATUS     | MESSAGE |
+--------------------------------+------+------------+---------+
| 1739913007764/01__0_0_0.csv.gz |   33 | DOWNLOADED |         |
+--------------------------------+------+------------+---------+
�[90m2025-02-18 16:10:11�[0m �[33mDBG�[0m �[36mREMOVE @"RESEARCH_DEV"."SLING_STAGING"/sling_temp/1739913007764�[0m
�[90m2025-02-18 16:10:11�[0m �[33mDBG�[0m opened "file" connection (conn-file-odY)
�[90m2025-02-18 16:10:11�[0m �[33mDBG�[0m reading single datastream from  [format=csv]
�[90m2025-02-18 16:10:11�[0m �[33mDBG�[0m merging csv readers of 1 files [concurrency=3] from
�[90m2025-02-18 16:10:11�[0m �[33mDBG�[0m processing reader from file://C:/Users/ilee/AppData/Local/Temp/snowflake/get/2025-02-18T161007.764/01__0_0_0.csv.gz
�[90m2025-02-18 16:10:11�[0m �[33mDBG�[0m casting column '_123col' as 'bigint'
�[90m2025-02-18 16:10:11�[0m �[32mINF�[0m writing to target database [mode: full-refresh]
�[90m2025-02-18 16:10:11�[0m �[33mDBG�[0m �[36mIF OBJECT_ID(N'"test"."test_int_col_sling_tmp"', N'U') IS NOT NULL DROP TABLE "test"."test_int_col_sling_tmp"�[0m
�[90m2025-02-18 16:10:12�[0m �[33mDBG�[0m table "test"."test_int_col_sling_tmp" dropped
�[90m2025-02-18 16:10:12�[0m �[33mDBG�[0m �[36mcreate table "test"."test_int_col_sling_tmp" ("_123col" bigint,
"123col" bigint)�[0m
�[90m2025-02-18 16:10:12�[0m �[32mINF�[0m created table "test"."test_int_col_sling_tmp"
�[90m2025-02-18 16:10:12�[0m �[32mINF�[0m streaming data
�[90m2025-02-18 16:10:13�[0m �[33mDBG�[0m bcp version is 15
�[90m2025-02-18 16:10:13�[0m �[33mDBG�[0m bcp 'test.test_int_col_sling_tmp' in 'C:/Users/ilee/AppData/Local/Temp/sqlserver.test.test_int_col_sling_tmp.1739913012955.NKA1.csv' -S '****' -d 'longhouse' -t ',' -m '1' '-w' -q -b '50000' -F '2' -e 'C:/Users/ilee/AppData/Local/Temp/sqlserver.test.test_int_col_sling_tmp.1739913013228.zoK.error' -U 'app' -P '****'
�[90m2025-02-18 16:10:14�[0m �[33mDBG�[0m �[36mIF OBJECT_ID(N'"test"."test_int_col_sling_tmp"', N'U') IS NOT NULL DROP TABLE "test"."test_int_col_sling_tmp"�[0m
�[90m2025-02-18 16:10:14�[0m �[33mDBG�[0m table "test"."test_int_col_sling_tmp" dropped
�[90m2025-02-18 16:10:14�[0m �[33mDBG�[0m closed "sqlserver" connection (conn-sqlserver-0lV)
�[90m2025-02-18 16:10:14�[0m �[33mDBG�[0m closed "snowflake" connection (conn-snowflake-lYC)
�[90m2025-02-18 16:10:14�[0m �[32mINF�[0m �[31mexecution failed�[0m

�[35mIf facing issues with Microsoft's BCP, try disabling Bulk Loading with `use_bulk=false`. See https://docs.slingdata.io/sling-cli/run/configuration#target�[0m


�[31mfatal:
--- proc.go:271 main ---
--- sling_cli.go:482 main ---
--- sling_cli.go:518 cliInit ---
--- cli.go:286 CliProcess ---
~ failure running replication (see docs @ https://docs.slingdata.io/sling-cli)
--- sling_run.go:217 processRun ---

--------------------------- research_dev.test_int_col_sling ---------------------------
--- proc.go:271 main ---
--- sling_cli.go:482 main ---
--- sling_cli.go:518 cliInit ---
--- cli.go:286 CliProcess ---
--- sling_run.go:215 processRun ---
--- sling_run..go:44 RunReplication ---
--- sling_run.go:519 replicationRun ---
--- sling_run.go:401 runTask ---
--- task_run.go:185 Execute ---


--- task_run.go:135 func2 ---
--- task_run.go:600 runDbToDb ---
--- task_run_write.go:260 WriteToDb ---
--- database_sqlserver.go:268 BulkImportFlow ---
--- database.go:2348 BulkImportFlow ---
~ could not bulk import
--- database.go:2339 func1 ---

--- database_sqlserver.go:393 func2 ---
~ SQL Server BCP Import Command -> bcp 'test.test_int_col_sling_tmp' in 'C:/Users/ilee/AppData/Local/Temp/sqlserver.test.test_int_col_sling_tmp.1739913012955.NKA1.csv' -S '****' -d 'longhouse' -t ',' -m '1' '-w' -q -b '50000' -F '2' -e 'C:/Users/ilee/AppData/Local/Temp/sqlserver.test.test_int_col_sling_tmp.1739913013228.zoK.error' -U 'app' -P '****'
SQL Server BCP Import Error  -> #@ Row 2, Column 1: Unexpected EOF encountered in BCP data-file @#



Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 17 for SQL Server]Unexpected EOF encountered in BCP data-file

BCP copy in failed

--- database_sqlserver.go:645 BcpImportFile ---
exit status 1

--- task_run.go:135 func2 ---
~ Could not WriteToDb
--- task_run.go:600 runDbToDb ---
~ could not insert into "test"."test_int_col_sling_tmp"
--- task_run_write.go:266 WriteToDb ---

--- task_run.go:135 func2 ---
--- task_run.go:600 runDbToDb ---
--- task_run_write.go:260 WriteToDb ---
--- database_sqlserver.go:268 BulkImportFlow ---
--- database.go:2348 BulkImportFlow ---
~ could not bulk import
--- database.go:2339 func1 ---�[0m

[flarco]

Hey @isaacrlee , I think it's something else. Below worked fine for me:

source: postgres
target: mssql

streams:
  test1k_mysql_pg:
    sql: select id as "1col", id as "_1col" from public.test1k_mysql_pg
    object: dbo.test1k_mysql_pg
    mode: full-refresh

2025-03-08 15:49:40 INF Sling CLI | https://slingdata.io
2025-03-08 15:49:40 INF Sling Replication | postgres -> mssql | test1k_mysql_pg
2025-03-08 15:49:40 DBG Sling version: dev (darwin arm64)
2025-03-08 15:49:40 DBG type is db-db
2025-03-08 15:49:40 DBG using: {"columns":null,"mode":"full-refresh","select":null,"transforms":null}
2025-03-08 15:49:40 DBG using source options: {"empty_as_null":false,"null_if":"NULL","datetime_format":"AUTO","max_decimals":-1}
2025-03-08 15:49:40 DBG using target options: {"datetime_format":"auto","file_max_rows":0,"max_decimals":-1,"use_bulk":true,"add_new_columns":true,"adjust_column_type":false,"column_casing":"source"}
2025-03-08 15:49:40 INF connecting to source database (postgres)
2025-03-08 15:49:40 DBG opened "postgres" connection (conn-postgres-gRv)
2025-03-08 15:49:40 INF connecting to target database (sqlserver)
2025-03-08 15:49:40 DBG opened "sqlserver" connection (conn-sqlserver-sm9)
2025-03-08 15:49:40 INF reading from source database
2025-03-08 15:49:40 DBG select id as "1col", id as "_1col" from public.test1k_mysql_pg
2025-03-08 15:49:40 INF writing to target database [mode: full-refresh]
2025-03-08 15:49:40 DBG IF OBJECT_ID(N'"dbo"."test1k_mysql_pg_tmp"', N'U') IS NOT NULL DROP TABLE "dbo"."test1k_mysql_pg_tmp"
2025-03-08 15:49:40 DBG table "dbo"."test1k_mysql_pg_tmp" dropped
2025-03-08 15:49:40 DBG create table "dbo"."test1k_mysql_pg_tmp" ("1col" integer,
"_1col" integer)
2025-03-08 15:49:40 INF created table "dbo"."test1k_mysql_pg_tmp"
2025-03-08 15:49:40 INF streaming data
2025-03-08 15:49:40 DBG bcp version is 17
2025-03-08 15:49:40 DBG bcp 'dbo.test1k_mysql_pg_tmp' in '/var/folders/49/1zc24t595j79t5mw7_t9gtxr0000gn/T/sqlserver.dbo.test1k_mysql_pg_tmp.1741466980373.93a1.csv' -S '****' -d 'master' -t ',' -m '1' '-w' -q -b '50000' -F '2' -e '/var/folders/49/1zc24t595j79t5mw7_t9gtxr0000gn/T/sqlserver.dbo.test1k_mysql_pg_tmp.1741466980394.Muh.error' -U 'sa' -P '****' -b '5000'
2025-03-08 15:49:40 DBG select count(*) cnt from "dbo"."test1k_mysql_pg_tmp"
2025-03-08 15:49:40 DBG IF OBJECT_ID(N'"dbo"."test1k_mysql_pg"', N'U') IS NOT NULL DROP TABLE "dbo"."test1k_mysql_pg"
2025-03-08 15:49:40 DBG table "dbo"."test1k_mysql_pg" dropped
2025-03-08 15:49:40 DBG create table "dbo"."test1k_mysql_pg" ("1col" integer,
"_1col" integer)
2025-03-08 15:49:40 INF created table "dbo"."test1k_mysql_pg"
2025-03-08 15:49:40 DBG insert into "dbo"."test1k_mysql_pg" ("1col", "_1col") select "1col" as "1col", "_1col" as "_1col" from "dbo"."test1k_mysql_pg_tmp"
2025-03-08 15:49:40 DBG inserted rows into "dbo"."test1k_mysql_pg" from temp table "dbo"."test1k_mysql_pg_tmp"
2025-03-08 15:49:40 INF inserted 1002 rows into "dbo"."test1k_mysql_pg" in 0 secs [2,505 r/s] [7.8 kB]
2025-03-08 15:49:40 DBG IF OBJECT_ID(N'"dbo"."test1k_mysql_pg_tmp"', N'U') IS NOT NULL DROP TABLE "dbo"."test1k_mysql_pg_tmp"
2025-03-08 15:49:40 DBG table "dbo"."test1k_mysql_pg_tmp" dropped
2025-03-08 15:49:40 DBG closed "sqlserver" connection (conn-sqlserver-sm9)
2025-03-08 15:49:40 DBG closed "postgres" connection (conn-postgres-gRv)
2025-03-08 15:49:40 INF execution succeeded

[flarco]

I see you're using v15. can you try with bcp version 17?