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.
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