Posts Tagged ‘SQL Server 2012’

FileTable and BULK Insert

January 21, 2014

In my video entitled Data Warehouse 12 ELT and FlatFiles  starting at the about the 10 minute, I demonstrate how to manage flat files with SQL Server FileTables.

While FileTables are great for managing flat files, I  failed to include and example of how to BULK insert data from a FileTable into a regular SQL Server table.

This is a very important step for data warehousing and unfortunately, as of the writing of this post, it is not documented at all! and in fact if you to simply preform a BULK insert of the data from the FileTable into a normal SQL table you will get the following cryptic error:

Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file “\\Garrett-hp\sql2012\FileTableDB\ETLFileTable_Dir\TestTable.txt” could not be opened. Operating system error code 50(The request is not supported.).

Not very helpful  and there is very little about it on the WWW Sad smile , hence the origin of this post.

There are several work-a-rounds to this issue none of which are perfect however my preferred method is to simply map the FileTable directory to  a drive on the OS and then magically BULK insert works!

see screen shot below of a working example:

image

select * from [dbo].[ETLFileTable];
truncate table  FileTableDB.[dbo].[TestLoadTable];
BULK INSERT FileTableDB.[dbo].[TestLoadTable]
 FROM 'Z:test.txt' /*mapped drive*/
 WITH(CHECK_CONSTRAINTS
       ,CODEPAGE='RAW'
       ,DATAFILETYPE='char'
       ,FIELDTERMINATOR='|'
       ,ROWTERMINATOR='\n');
select * from FileTableDB.[dbo].[TestLoadTable];

Additionally, I would caution against using SSIS just to do the insert, especially if SSIS is installed on another server!?

The FileTable data already in the  SQL Server filegroup on the DW server would have to leave the server, traverse the network, be processed by SSIS, then return over the network again, just to be inserted into a table on the DW!!!

Avoid all that and just do a BULK insert. For those who are interested SSIS in FastMode simply does a BULK insert:

Jamie Thompson: SSIS: Destination Adapter Comparison

“Where OLE DB Destination uses an access mode of OPENROWSET, OLE DB Destination with FastLoad uses OPENROWSET with FastLoad.

FastLoad means that the adapter uses BULK INSERT for the insertion.”