I had a file (historical FTSE values) to load into a database for some development work so I started up the SSIS Import and Export Wizard to bring it in. There was no error but after three or four attempts it was still failing to bring any rows in. Although I’ve done a lot of ETL I’ve generally used utilities or import exes, so this seemed like a opportunity to get my head around BULK INSERT and bcp.  There were a few minor issues that slowed me up a bit so I thought I’d jot down some notes for future reference on how I got it done.

Obviously language settings can cause problems so the script below looks at that first. Technical notes are in the code comments:

Database Engine:
Microsoft SQL Server 2014 - 12.0.2254.0 (X64)
Jul 25 2014 18:52:51
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )


/*
--a table that will house the data is the following:
CREATE TABLE dbo.StgMarketIndex
(
DateReference date NOT NULL,
Opening decimal(18, 0) NOT NULL,
High decimal(18, 0) NOT NULL,
Low decimal(18, 0) NOT NULL,
Closing decimal(18, 0) NOT NULL,
Volume decimal(18, 0) NOT NULL,
AdjClosing decimal(18, 0) NOT NULL
)
*/

--check what we've got it was us_english in our case
SELECT SYSTEM_USER, @@LANGUAGE,@@LANGID

--View languages, most relevant columns are langid and name
EXEC sys.sp_helplanguage

/*
--one suggestion was the following but it's deprecated in Sql Server 2014
--http://msdn.microsoft.com/en-us/library/ms187378.aspx

DECLARE @sysuser NVARCHAR(30)
SET @sysuser = SYSTEM_USER
PRINT @sysuser
EXEC sp_defaultlanguage @sysuser, 'british'
*/

--recommended method is the following--
DECLARE @User sysname = SYSTEM_USER
DECLARE @Cmd nvarchar(100) = 'ALTER LOGIN' + ' ' + '"' + @User + '"' + ' ' + 'WITH DEFAULT_LANGUAGE = British'
PRINT @Cmd
EXECUTE(@Cmd)

--default language for the user changes but current session continues with original language
SELECT SYSTEM_USER, @@LANGUAGE,@@LANGID

--to make a change immediately run the following
SET Language British


--this should now work provided you have stripped the column headings out of your file--
BULK INSERT StgMarketIndex
FROM '{file path from database server}'
WITH (
FIELDTERMINATOR = ','
,ROWTERMINATOR = '\n'
)
GO

As you can see in the code comments, I stripped the column headers from the file just to get it working. You’ll probably want an alternative method that copes with file headers, but if so you may not be able to use BULK INSERT. There’s a parameter, “FIRSTROW”, which looks tempting but the documentation rules it out, as follows: “The FIRSTROW attribute is not intended to skip column headers. Skipping headers is not supported by the BULK INSERT statement.” When I defined FIRSTROW as 2 it got rid of the type errors from the header values but nothing was inserted. It seems that the bcp format file firstrow setting works in a similar way. There’s more detail here but there was no obvious problem with my file so it seems you’d have to do a lot of investigation to get around the problem.

OPENROWSET with text driver specified and a schema.ini file in the local directory might do the trick but when I tried it I got a permission error so I didn’t explore further. This was an ad hoc import and for more permanent solutions I’d be looking to SSIS.

Still with the “no header” file, I also created a bcp format file by running the following in a command prompt on the database server:
bcp AcsDW.dbo.StgMarketIndex format nul -c -t, -f MarketPrice.Fmt -T

This worked with OPENROWSET as follows:
SELECT a.* FROM OPENROWSET( BULK '{data file path from database server}',
FORMATFILE = '{format file path from database server}') AS a;

The format file approach is probably the way to go if there are other issues to deal with such as file has delimiters embedded in the data for example.