SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 OLEDB Connection Manager & mysterious crashes mid
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Drammy
Starting Member

13 Posts

Posted - 09/12/2012 :  08:14:11  Show Profile  Reply with Quote
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

Edited by - Drammy on 09/12/2012 08:14:30

RickD
Slow But Sure Yak Herding Master

United Kingdom
3608 Posts

Posted - 09/12/2012 :  08:24:07  Show Profile  Reply with Quote
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.

Edited by - RickD on 09/12/2012 08:25:16
Go to Top of Page

Drammy
Starting Member

13 Posts

Posted - 09/12/2012 :  08:56:23  Show Profile  Reply with Quote
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?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000