• Post Categories

  • Browse Blogs

  • Blog Stats

    • 607,155 hits
  • Syndications

    SQLServerPedia Contributor

SSIS errors: Bulk Load failed. Cannot obtain the required interface (“IID_IColumnsInfo”) from OLE DB provider “BULK” for linked server “(null)

When working with the Bulk Insert Task in SSIS 2008 you may get the following error:

 The complete error message is:

[Bulk Insert Task] Error: An error occurred with the following error message: “Cannot obtain the required interface (“IID_IColumnsInfo”) from OLE DB provider “BULK” for linked server “(null)”.The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.”

This error description can make you jump through hoops trying to figure out why is it detecting an error with the OLE DB provider or why does SSIS thinks you are trying to execute this operation on a linked server?

The real issue here has nothing to do with the first  sentence in the error description. Sentence 4 gives you the actual error:

Verify that the field terminator and row terminator are specified correctly.

You may be experiencing this error due to one or more of the following 3 reasons:

1) You may be specifying a wrong CommonDelimiter for your source file.
For example, You may be trying to do a Bulk Insert operation from a Comma Separated Value (CSV) file but did not change the CommonDelimiter property to Comma {,}. When you drag in the Bulk Insert Task the CommonDelimiter property default value is Tab.

2) You may be specifying a wrong RowDelimiter for your source file.
For example, you may be trying to do a Bulk Insert operation from a Comma Separated Value (CSV) file whose row delimiter character is different than the RowDelimiter property default value of {CR}{LF}. In some cases, you may receive a file with a very long stream of text with no Carriage Return (\r)  & Line Feed (\n) characters, commonly denoted as {CR}{LF} in between rows. These hidden {CR}{LF} row delimiter characters are placed on a text file each time you hit the ENTER key on your keyboard denoting the end of a row and beginning of the next row. You may read a little more about the Carriage Return and Line Feed characters in Pinal Dave’s blog: http://blog.sqlauthority.com/2009/07/01/sql-server-difference-between-line-feed-n-and-carriage-return-r-t-sql-new-line-char/

As seen on the image below, there are two properties,  CommonDelimiter and RowDelimiter, that you need to make sure  you specify the correct values for depending on your input or source file format:

.
3) You may be using a format file with an incorrect or invalid format defined.
Format files can be non-xml, commonly with an *.fmt extension or for SQL Server 2005 and later only you can also use xml format files. For more information about format files read MSDN Books on Line http://msdn.microsoft.com/en-us/library/ms191516.aspx

If you are using a format file make sure you are pointing to the right format file or that the format defined in your format file is correct. (Notice that the Format property value changes from Specify to Use File):

SSRS errors: (rsReportServerDatabaseUnavailable) The report server cannot open a connection to the report server database.

Sometimes you may get this error when trying to launch your report server webpage:

The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. (rsReportServerDatabaseUnavailable)

While the error is descriptive enough to let you know that Reporting Services could not find the report server database, it may be a challenge to some who have no idea where this database is and what is it used for. To your surprise, there is more than one database involved.

Reporting Services requires two databases to run:

  • ReportServer
  • ReportServerTempDB

In short, the Report Server database is needed to store report definitions, report history and snapshots, report models, shared data sources, resources, scheduling and delivery information, metadata and several other objects. ReportServerTempDB is where Reporting Services stores session and execution data, cached reports, and work tables that are generated by the report server. For more information about Reporting Services database visit: http://msdn.microsoft.com/en-us/library/ms156016.aspx.

To solve the error mentioned above you need the following information:

  1. What is the SQL Server Reporting Services instance you are trying to reach?
  2. Where are the SQL Server Reporting Services databases hosted?

Assuming you have the right permissions, you will need to run the Reporting Services Configuration Manager and point to the Reporting Services instance as seen on the image below. You would normally find  Reporting Services Configuration Manager under Start->Programs->Microsoft SQL Server 2008 R2->Configuration Tools

Once you connect to your Reporting Services instance through Reporting Services Configuration Manager, go to the  Database tab located on the left pane to view the name of the SQL Server instance where the ReportServer database is being hosted and the name of the ReportServer database as seen in the image below.

Keep in mind that in some environments the ReportServer database may be hosted in a separate SQL Server database instance and the ReportServer database may have been renamed to other than the default “ReportServer” database name. You will usually find this type of configuration in a scale-out architecture. For more information on scale-out architecture visit: http://sqlcat.com/technicalnotes/archive/2008/06/05/reporting-services-scale-out-architecture.aspx.

Once you located the SQL Server database instance where the ReportServer and ReportServerTempDB database are hosted you need to follow this checklist:

  1. Is the SQL Server database instance running? Is the SQL Server service started?
  2. Can you verify connectivity to the SQL Server database instance?
  3. Are the ReportServer and ReportServerTempDB databases attached and online?
  4. Does the Reporting Services service account have read/write access to the ReportServer and ReportServerTempDB databases?

Once all of this is verified to be true and issues have been fixed, than the last step would be to restart the Reporting Services instance. If everything is right you should be able to access your reports. It is highly recommended to frequently backup both the ReportServer and ReportServerTempDB databases. If you lose you ReportServer database you may re-deploy reports, data sources and datasets, but you will lose all subscriptions, schedules and report parts that users may have created.

Also, keep in mind that the ReportServerTempDB database does not behave like the SQL Server TempDB. Per MSDN Books on Line:

“Reporting Services does not re-create the temporary database if it is missing, nor does it repair missing or modified tables. Although the temporary database does not contain persistent data, you should back up a copy of the database anyway so that you can avoid having to re-create it as part of a failure recovery operation.” For more details visit: http://msdn.microsoft.com/en-us/library/ms156016.aspx

 

%d bloggers like this: