Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Drammy
Starting Member
13 Posts |
 Posted - 2012-09-12 : 08:14:11
|
| Hi all,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?Thanks,Drammy |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2012-09-12 : 08:24:07
|
| 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. |
 |
|
|
Drammy
Starting Member
13 Posts |
Posted - 2012-09-12 : 08:56:23
|
| Thanks for the quick response RickD.To the best of my knowledge its happened at 3 sites and each have been running different configurations. Unfortunately I cannot be precise about this as the Clients would need to be contacted.Definitely Windows Server 2003 & 2008 (not sure which versions/editions). I also recreated it on my Windows 7 Ultimate x64 system - although I know that is not supported...I doubt it will be corrupted or old files as most of these systems had been freshly built and updated prior to the product's installation? |
|
|
|
|
|
|
OLEDB Connection Manager & mysterious crashes mid