SQL Server: Failed to execute query. Error: Cannot obtain the required interface (“IID_IColumnsInfo”) from OLE DB provider “BULK” for linked server “(null)”.

TL:DR This is what happens if you have more columns in your data source than you have in your format file when using T-SQL’s OPENROWSET or BULK INSERT command.

If you need to bulk load data into SQL Server then you have several options but assuming you can get that data as CSV then either OPENROWSET or BULK INSERT will handle this for you.

It will work for data on filing systems but also for data stored on Azure Storage Account Blob Containers and this is exceptionally useful, and when dealing with Azure SQL Databases much, much faster. However these commands really show their age as they come from a time of binary files and their formatting has grown over the years.

As we use them here at Simple Innovation we are finding a lot of errors that aren’t that explicit normally caused by mismatches between the data and format files.

One we found is:

Error: Cannot obtain the required interface (“IID_IColumnsInfo”) from OLE DB provider “BULK” for linked server “(null)”

This one is simple enough its caused by having more columns in the data file than you have the RECORD section of the format file.

So if we have a CSV file:

And an XML format file:

As the data file has nine fields and the format file is expecting ten columns when you run the following command:

You get the following error:

Failed to execute query. Error: Cannot obtain the required interface ("IID_IColumnsInfo") from OLE DB provider "BULK" for linked server "(null)".

If you remove the FIELD and COLUMN definitions for ID and SOURCE of 10 then it will work.

Alternatively add the tenth field to the CSV file and it will all work.

Good luck.

TL:DR This is what happens if you have more columns in your data source than you have in your format file when using T-SQL’s OPENROWSET or BULK INSERT command.