I am using SQL 2008 and an external vendor change the particular warehouse name for the IMPORT file that I was receiving through my SQL server from them. I get an error under the SQL Server Agent stating the IMPORTFROMXXX has failed. When I open the Job that is running notice that the warehouse name under the import file script is incorrect when I change it to the new name it still fails. How do I go about correcting this?
a) The old name is being used some other places that you have not yet found. I don't know where that might be - but it may be that the SQL Server Agent is be running an SSIS package. You may need to examine the SSIS package.
b) The vendor secretly changed the format of the IMPORT file. Strange as it may sound, I have had vendors do that to me vexing me to no end.
I am only guessing that it might be an SSIS job - they could be using something else like bcp or bulk insert. You have to examine the job steps to figure out what they are using. If it is an SSIS package, the information on this MSDN page may be helpful: http://msdn.microsoft.com/en-us/library/ms141701.aspx#trouble
Copy the SSIS package and simulate the same in Dev environment and change the file name to new name being used. If the file name generated is dynamic then you have to change in variables or else change the static file name where ever it appears on the job.