I am designing an ETL process where a .CSV file is loaded into a SQL Server table.

The table only contains a single 'load' of data at any one time. At the start of the process the table is dropped and re-created. The file is then loaded using bulk insert.

The script contains the instructions to DROP, CREATE the table, and then do a BULK INSERT. One of the reasons for doing it this way is to ensure that the table structure will always be correct.

My script looks like this (not all columns included for brevity)

DROP TABLE IF EXISTS [etl].[Auth_Daily];

CREATE TABLE [etl].[Auth_Daily]
(
    [RequestID] [varchar](50) NOT NULL,
    [Extracted] [smalldatetime] NOT NULL
)

SET DATEFORMAT DMY

BULK INSERT [etl].[Auth_Daily]
FROM 'C:/import/auth_daily.csv'
WITH (FIRSTROW = 2,
      FIELDTERMINATOR = ',',
      ROWTERMINATOR='\n',
      FORMAT='CSV',
      TABLOCK);

This works perfectly if the table either doesn't exist to start with, or has the same structure. However, if the table exists with a different structure (for instance the [Extracted] column is removed from the exiting table), then the query fails with:

Cannot obtain the required interface ("IID_IColumnsInfo") from OLE DB provider "BULK" for linked server "(null)".

The error (although very unhelpful!) is the same as if I had not dropped and re-created the table. Therefore I am assuming that the BULK INSERT process is actually looking at the original state of the table (prior to the drop and create).

I have tried placing Start/Commit Transaction around both the Drop/Create and Bulk insert sections but still get the same error.

Is there anything I can do to make this work as one query? I know I could execute the DROP/CREATE as one query, then the BULK INSERT as a separate one, but this rather defeats the object of having the table structure and the bulk insert defined in one .sql script?

Any suggestions appreciated.

I am designing an ETL process where a .CSV file is loaded into a SQL Server table.

The table only contains a single 'load' of data at any one time. At the start of the process the table is dropped and re-created. The file is then loaded using bulk insert.

The script contains the instructions to DROP, CREATE the table, and then do a BULK INSERT. One of the reasons for doing it this way is to ensure that the table structure will always be correct.

My script looks like this (not all columns included for brevity)

DROP TABLE IF EXISTS [etl].[Auth_Daily];

CREATE TABLE [etl].[Auth_Daily]
(
    [RequestID] [varchar](50) NOT NULL,
    [Extracted] [smalldatetime] NOT NULL
)

SET DATEFORMAT DMY

BULK INSERT [etl].[Auth_Daily]
FROM 'C:/import/auth_daily.csv'
WITH (FIRSTROW = 2,
      FIELDTERMINATOR = ',',
      ROWTERMINATOR='\n',
      FORMAT='CSV',
      TABLOCK);

This works perfectly if the table either doesn't exist to start with, or has the same structure. However, if the table exists with a different structure (for instance the [Extracted] column is removed from the exiting table), then the query fails with:

Cannot obtain the required interface ("IID_IColumnsInfo") from OLE DB provider "BULK" for linked server "(null)".

The error (although very unhelpful!) is the same as if I had not dropped and re-created the table. Therefore I am assuming that the BULK INSERT process is actually looking at the original state of the table (prior to the drop and create).

I have tried placing Start/Commit Transaction around both the Drop/Create and Bulk insert sections but still get the same error.

Is there anything I can do to make this work as one query? I know I could execute the DROP/CREATE as one query, then the BULK INSERT as a separate one, but this rather defeats the object of having the table structure and the bulk insert defined in one .sql script?

Any suggestions appreciated.

• sql-server
• t-sql
• transactions
• bulkinsert

• 1 "the bulk insert defined in one .sql script?" SQL files can have batch separators in them too. – Thom A Commented Mar 16 at 18:10
• Thanks @Thom, have accepted Gordy's answer - can't believe I had overlooked something so simple! Thanks Dan as well for the alternative approach. Hopefully others will find this useful. – JHW Commented Mar 17 at 8:31

2 Answers 2

I am assuming that the BULK INSERT process is actually looking at the original state of the table (prior to the drop and create).

Yes, it seems the BULK INSERT statement is compiled as part of the batch so a table schema change afterwards causes the error during execution.

A workaround to keep the statements in the same script is to execute the `BULK INSERT` statement using dynamic SQL so that the statement is compiled after the schema change:

DROP TABLE IF EXISTS [etl].[Auth_Daily];

CREATE TABLE [etl].[Auth_Daily](
    [RequestID] [varchar](50) NOT NULL,
    [Extracted] [smalldatetime] NOT NULL
);


SET DATEFORMAT DMY

EXEC sp_executesql N'
BULK INSERT [etl].[Auth_Daily]
FROM ''C:/import/auth_daily.csv''
WITH (FIRSTROW = 2,
   FIELDTERMINATOR = '','',
   ROWTERMINATOR=''\n'',
    FORMAT=''CSV'',
    TABLOCK);';

put a batch separator here:

)
GO
SET DATEFORMAT DMY

if you're executing the script programmatically it's the equivalent of running the two parts separately on the same session