Job Failure Mystery: Package Uses Missing Dir Yet Ran Fine Last Week

[fuzzyocelot]

I have a bit of a mystery on my hands that I hope someone can help me with. I'm a newbie database administrator so please go easy on me. We’re running SQL Server 2000 on a Windows Server 2003 Enterprise Edition with SP1.

A routine job failed this morning. The job runs a package once a week.

Here is what the package does:

1) Connects to an ftp server and downloads a file into a directory via an execute process task. Let’s call the directory "directory A".
2) Then it truncates some tables via an execute SQL task.
3) Next it reads the file from directory A using text file source.
4) A transform data task is used next to read that file into a table.
5) If successful, a certain stored procedure is executed via an execute SQL task. Nothing is set to happen if it fails.
6) Then another table is read into a separate file. A transform data task is set between the database connection and the text file destination. It just copies a table into a file.
7) Upon completion, an email is sent out to certain people.

Here’s the mystery. Hopefully I can explain it with little confusion. Within the first transform data task that reads the file that was downloaded, the source file is set to directory B instead of A. This directory B does not exist on the server. Directory B is not referenced anywhere in the entire package! Only directory A is used. According to the logs, this package was last changed on 10/17/2005. Yep! 2005! According to the job activity logs, this package has successfully run in the past. It runs once a week with no errors and it ran last week just fine! How can this package run successfully with the wrong directory set as the source for the data transform task? The only thing I can think of is that the directory B used to exist on the server but someone deleted it recently. If that’s true, then either someone has been manually putting the file there every week or the package has been using the wrong file all this time! What also confuses me is that the directory B does not exist on my computer. Yet when I open the transform data task using the client tools on my computer and do a preview on the file, it shows me data for the file. I hope I haven’t confused anyone. Does this make any sense to anyone? Does anyone have any ideas as to what’s going on here or how I can find out?

Thanks!

 

[mrdenny]

When you open the package on your machine and check the path it's using a file stored on your local machine to get the file from. When it runs on the server it uses the servers path to get the file.

If there is a dynamic transformation properties task it could be changing the path to the file dynamically when the package runs.

It is also possible that the folder was deleted. Recreate it and see if the job and package start working correctly.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[fuzzyocelot]

mrdenny, thank you for replying.

I double-checked the package, and there isn't a dynamic transformation properties task in it.

I understand that if I open or run packages on my machine, it will use paths local to my machine. If I run packages on the server, it will use the paths on the server. What I don't understand is that when I opened the package on my machine and checked the path (which was "directory B"), I didn't get any errors since that path doesn't exist on my machine. In fact, I was able to preview the data.

I was going to test the package by recreating the folder that was probably deleted but one of the other DBAs changed the package to use the correct path instead. I guess I just need to chalk it up to one of those things. I may try coping the package to our test server and see if I can recreate the problem.

Thanks!
Rebecca