Just as I thought I could remove the phrase “DLL Hell” from my vocabulary, I had a nasty DLL issue today with SQL Server. After installing SP3 for SQL Server 2005 over the weekend, users started complaining about jobs failing today. After further investigation, it was only jobs that were running SSIS packages. After looking at a few packages it seemed that only the Data Flow task was having issues. You could not even create a new Integration Services project and drag the Data Flow task over without getting the following error.
"Failed to create the task.
The designer could not be initialized. (Microsoft.DataTransformationServices.Design)"
The closest thing I could find to the issue I was having was this thread on msdn (http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/f2659cec-cee3-419d-8c70-c40c50bf16ad). After trying to register the dtspipeline.dll I received yet another error message “Invalid access to memory location”. At this point I wanted to just reinstall Integration Services and see if that would fix the problem, but this was on a production server and I wanted to make sure that I didn’t impact any of the users or interrupt any of the processes that were still working. I renamed the bad dtspipeline.dll and copied a new one with the same version from a machine that was working and registered it using regsvr32. The DLL registered successfully and the jobs started working again.
I wanted to document this because I could not find a lot of cases of this issue logged online. I also could not find any resolutions other than trying to reinstall Integration Services. Hopefully this may save someone else some time in the future.