Sometimes it happens. SQL Server Data Tools crashes and burns. (It’s also known as Visual Studio or SSDT but I don’t know how to pronounce that…) Was it something I said? I’m currently building a small data warehouse and building out a lot of packages in a fairly short period of time, so maybe I was wearing out good ole’ SSDT. After setting up a new package, I tried to execute it and the application hung. I waited patiently. Nothing. Waited some more, and then decided I had no choice. I had to put SSDT out of its misery and (hide the children) kill the process. Now this was something that I encountered often enough when working with earlier versions of SQL Server using Business Intelligence Development Studio (or the more pronounceable BIDS), but SSDT has been much more stable for me. Until this moment.
After reopening SSDT, I tried to run my package, but got this error:Failed to execute package or element. Build errors were encountered. For more information, see the Output window.
I’m good at following directions. This is what I found in the Output window:
------ Build started: Project: DW_ETL, Configuration: Development ------
Build started: SQL Server Integration Services project: Incremental ...
Starting project consistency check ...
Project consistency check completed. The project is consistent.
Error : System.IO.IOException: The process cannot access the file
because it is being used by another process.
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.File.Delete(String path)
Build complete -- 1 errors, 0 warnings
========== Build: 0 succeeded or up-to-date, 1 failed, 0 skipped ==========
And for good measure, I also looked in the Error window which contained essentially the same message.
The ISPAC file is necessary for deploying a project to the SSISDB catalog. It gets built automatically when you execute a package in SSDT, or you can manually build it by using the Build command in SSDT. Its purpose is to consolidate all the packages and parameters for your project (assuming you are using the default Project Deployment mode), so that you can deploy the entire project to the SSISDB database as a single unit. In other words, you can hand off one file to an administrator of the Integration Services server and they can deploy it onto the server.
During the development process, I don’t need the ISPAC file. And if it’s being used by another process, ostensibly the one I just killed, but blocking my current process, I should just kill it, too. The problem I found is that it was good at self-defense. When I tried to delete the ISPAC file, I got the following message:Action can’t be completed because the file is open in SSIS debug host.
Okay, fine, I closed SSDT and tried to delete the ISPAC file again. Same message.
Next I went to Task Manager and found two processes. DTSDebugHost.exe and DTSDebugHost.exe *32. Can you guess what I did? I had to be pretty ruthless yesterday. I don’t feel good about it, but it had to be done. After the deed was done, I never looked back. I reopened SSDT and ran my package successfully. All’s well that ends well.