Thursday, April 9, 2009

Job hangs when running SSIS package

I had a SSIS package that I modified and put out onto a development server. When I ran the existing job, the job would hang. Based on troubleshooting, it would appear the package would not even begin to start. When the package was executed from the development PC, it ran fine. When remoted into the SQL Server and ran the package in BIDS, it ran fine. However, when we ran in a command line the command that was in the job step, it would produce the following error. Note that the package would in fact run though under this method.

The file name is not valid. The file name is a device or contains invalid characters.

It turns out the problem was the fact that the package used a configuration file and the config file specified the location of where the checkpoint file should be written. The config file on the server had the checkpoint file location as the developer’s workstation and not of the location for the development environment. Upon changing the config file, the job would then run the package successfully. It would appear that when this type of error is encounter in a job, the job would hang and not handle the exception correctly.

No comments: