I have an SSIS package that was originally written on SQL Server 2005.
One of the tasks is a data load which basically calls a stored procedure . The stored proc execute some SQL which drops and recreates a number of database tables and then scans a directory for data files and then bulk loads each data file into the newly created tables. Each bulk load was configured to create a log file should any load exceptions occur.
Logging was set up to log to event viewer and to a SQL table. All fairly standard stuff.
All task connections were made via an OLEDB connection manager.
When SQL Server 2008 came out we upgraded the package and ran a number of tests and all seemed fine. I then went to install the package on a Client's site and we hit a problem with this data load stage of the SSIS package.
We found that after a number of records, random but frequently within a certain range, the load process literally crashed out, with the next task in the SSIS package starting up as normal. I say it crashed out because I noticed the log files from the bulk loads were literally half written; it created a log file as standard for a bulk load which would have been deleted after the successful load but only wrote a fraction of the data out to the file.
If I executed the stored procedure on its own then it worked fine however it consistently failed from within the SSIS package.
I searched high and low for similar symptoms on the internet without any joy and after a week or so of investigation I found that simply changing the connection manager to an ADO.NET connection manager (remember it was OLEDB) resolved the issue.
I am in a position where I need to better explain this to Clients and am unsure exactly what the problem is. So...
Has anyone encountered or heard of similar issues? Does anyone have a clue what the problem may have been?
This could be one of many issues with OLEDB, it could be as simple as missing/corrupted .dll files, a old copy of MDAC, missing/old JET .dlls or even down to the OS you are using. If you could give more information about the machine this instance of SQL is on, you are more likely to find someone who may have come across the issue before.