Silent truncation using OpenRowSet to Bulk Load data

Question

Silent truncation using OpenRowSet to Bulk Load data

vikasjagadale8

Say Hey Kid

Points: 704
More actions
August 29, 2017 at 11:53 am

#332444

Hello,
I have to load CSV data into SQL server table using OpenRowSet.
I have installed the AccessDatabaseEngine_X64.exe, Access and SQL server both are 64-bit.
Also enabled below settings,
sp_configure 'show advanced options', 1
reconfigure with override
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure with override
INSERT INTO dbo.Test
SELECT * FROM OPENROWSET('MSDASQL'
,'Driver={Microsoft Access Text Driver (*.txt,*.csv)}','select * fromD:\MYDATA\go\test.CSV')
Data was also getting loaded into the table, but some of my rows in CSV have data more than what is defined in the schema of the table (Test) and I don't want to change column size in the table(Test) So SQL started giving errors related to truncation. Without using OpenRowSet earlier I used to use "Set ANSI WARNINGS OFF" to do silent truncation. But with OpenRowSet if I use this command then it gives below error
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.
I want to use OpenRowSet because it is very fast. So can anyone please help me on how I can do silent truncation using OpenRowSet.
Thanks & Regards
Vikas Jagadale

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110640 More actions · Answer 1

You can wrap your OPENROWSET into an SSIS package and then either use a derived column transformation, or just set the necessary component settings to not fail that element due to truncation.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

vikasjagadale8 Say Hey Kid Points: 704 More actions · Answer 2

Thanks for the reply. But I am using Talend 6.3.1 ETL tool. Where in a tMsSqlRow component I am placing the OpenRowSet script.

sgmunson SSC Guru Points: 110640 More actions · Answer 3

Okay, but if your tool isn't doing the job, why use it? SSIS can accomplish the task effectively. Alternatively, perhaps you can look at the Talend documentation and see if that tool provides a way to override whether or not truncation causes failure. This is a SQL Server forum, so that answer is unlikely to be found here.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Avi1 SSCrazy Points: 2323 More actions · Answer 4

For bulk load BCP is very good option you can simply write it is SQL job and schedule it as well