SSIS Tips and Tricks
Working on an SSIS package today I ran into two problems:
Cannot fetch a row from OLE DB provider “BULK” for linked server
Although a set of possible solutions is provided on the MSDN forums here, none of the ones I tried worked in my case. It seems that this error may also come up if you are using an OLE DB source as one of the sources of the transformation and a SQL Server destination.
I managed to fix the problem by using an OLE DB destination instead of a SQL Server destination. No big difference between the two, but it seems SSIS raises this error for SQL Server destination.
Class not registered (when using an Excel source)
If you are using an SSIS transformation with an Excel source, the above error occurs when trying to run in 64 bit mode. This, I found out, is because the Jet engine (the database engine behind Excel, Access etc.) doesn’t have any 64 bit drivers. The Visual Studio designer runs in 32 bit mode, so there will be no problems at design time but when running, if the machine is a 64 bit one, you will get this error.
To correct this, disable the 64 bit runtime for your project. Go to Project -> Properties, select Debugging and set Run64BitRuntime to False.