Silent truncation using OpenRowSet to Bulk Load data

SSIS and Import/Export
1

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')
‌‌

D‌ata 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.

2

Try using OpenRowset(BULK
It's faster then the method you're using and you can create a view against your file with substring on each column with the length of the target table.

3

Thanks for the prompt reply. But creating views from csv will be time consuming. because 1st I will have to create views from csv which will load data into target table. 2nd adding substring for each column in the view will be again a pain because there will be more than 50 target tables each having minimum 300 columns.

4

So write a script to dynamically generate the views based on the column lengths.
You could map the tables to files in a table.