Solved

Execute process task error

Posted on 2009-04-01
23
1,524 Views
Last Modified: 2013-11-10
I am calling an .bat file from a execute process task wheni run it manually it  works fine but if i schedule it form a job it says that xyz..bat file does not exist. But i chjecked it many times the file exisits in the specified folder i dont know why i am gettign this error. Please look at the error

Description: File/Process "xyz.bat" does not exist in directory "C:\path".  End Error  Error: 2009-04-01 10:55:33.86     Code: 0xC0024107     Source: Extract File from FTP      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).
0
Comment
Question by:svs1919
  • 12
  • 11
23 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24045415
I assume this is related to this:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/Q_24272183.html
It helps to post a reference to any prior question which may have some bearing.
Your error occurs because when you run the package as a job, its running it from the SQL Server, and that file does not exist on C on the server.
 
 
0
 

Author Comment

by:svs1919
ID: 24049665
So do you suggest me to add servername infront of the path does that solve the problem or what can i do to over come this isse.
0
 

Author Comment

by:svs1919
ID: 24054358
every time i run the package i am getting a new error. Now  i run i got the following error
File/Process "xyzbat" does not exist in directory "c:\Path".

But the  fact is the.bat file does exist in the path. I reacheked many times i am not sure why this is happening.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24055880
When it runs as a job, it  runs
1. On the server that the SQL Agent is running
2. As the user that the SQL Agent is running under
If C:\path\xyz.bat does not exist under either of those circumstances, you will get that error.
Can you firstly clarify: is the SQL Agent running on a different server to where you run it on manually?
0
 

Author Comment

by:svs1919
ID: 24060400
no both are on same server.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24070754
0
 

Author Comment

by:svs1919
ID: 24080780
yes this is related to abouve link . As i before  every time i run the package i am getting a new error.  I ran the package  i got the following error  File/Process "xyzbat" does not exist in directory "c:\Path".
But the  fact is the.bat file does exist in the path. Then i came to know that on server every user has a personel folder so this .bat file is in my folder so when  i try to run this package  the SSIS is not able to access this folder. So to avoid this i  replaced this .bat file in a C: drive then it  worked but other error occured this .bat file is used to bring excel files from a FTP site and put it ina  local folder. So when i get this file i am using a task to take data from this excel file and move it to  SQL table. Now the error is it says that " ABC.xls file   is already opened exclusively by another user, or you need permission to view its data.".. So what can i do to avoid this ERROR .
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24082330
So now you have resolved this issue and you are getting an error related to importing Excel files? Have you copied the Excel files somewhere locally? (i.e. c:\path)
0
 

Author Comment

by:svs1919
ID: 24083028
yes i did . what could be the reason for that?
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24083189
Did you copy them manually or did you do it as part of the job?
It is most likely still related to the SQL Server Agent user.
Go to
Start/Settings/Control Panel/Administrative Tools/Services
Find the SQL Agent Service in there. Go to the Log On tab. Put in an account that has access to the path that the Excel files are in and try again.
Alternatively it may be that there is some kind of protection in the Excel sheet, or maybe the file copying process is not complete.
0
 

Author Comment

by:svs1919
ID: 24087169
in logon tab if i put  my  username  there are other scheduled jobs that are  running will it  cause any problem to other  jobs as its a dev server and many users are running their jobs on this server.

And there is no protection for excel sheet as it runs  fine if i run it manually
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24095293
will it  cause any problem to other  jobs
Quite possibly.
Did you copy the Excel files locally, and did you do that as a step in your job?
0
 

Author Comment

by:svs1919
ID: 24101401
The files are copied locally.actually i copy the data from excel using a transformation .
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24103003
Did you copy the files tp a local path as step in your job, or did you copy the files yourself manually?
0
 

Author Comment

by:svs1919
ID: 24103471
no i am not doing it manually . Its all done using dataflow tasks
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24103924
No, you cannot copy a file using a data flow task. How do you actually get the Excel file (.XLS) on to the SQL Server. Do you go into windows explorer and copy it yourself, or do you run an execute process or file task in SSIS to copy the file?
Are you 100% certain no one has the file open when it is trying to import it?
0
 

Author Comment

by:svs1919
ID: 24106846
I have an execute process task that brings .xls files from a FTP site and puts in local folder. Now i  have  a dataflow task which has  Source as Excel Source and Destination as oledb destination. Now excel connectionmanager has an expression that is used to look for a appropriate file and get its data into SqL  table.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24114988
OK, so the job is able to copy the file there, thats good. That at least tells me that the SQL Agent has enough rights to copy the file there.
Are you actually able to open the Excel files yourself?
 
 
0
 

Author Comment

by:svs1919
ID: 24115244
yes  i am able to open .xls files
0
 

Author Comment

by:svs1919
ID: 24132265
any further suggestions nmcdermaid:
0
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 500 total points
ID: 24134000
Does your data flow occur immediately after the .bat file FTP's them locally? I'm guessing the FTP/copy is not finished when you try to import it. The FTP/copy has the file locked.
What I suggest you do is put a delay into your SSIS. You can do this by running
WAITFOR DELAY '00:05';
in an execute SQL task, after the FTP step and before the data pump task. That will wait five minutes.
You could also copy a 'completion' file after all of the Excel files. When you see this completion file you know the Excel files are completed copying.
 
0
 

Author Comment

by:svs1919
ID: 24137677
yes now my FTP task is working fine. The next step of copying data from excel to SQl is alos  working fine . But the other problem is i have one more execute container task that runs an .exe file that task is also working fine but it is not doing what it is supposed to do. How can i fix this
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24143394
that task is also working fine but it is not doing what it is supposed to do. How can i fix this
Can you reread the sentence above and tell me how on earth I'm meant to troubleshoot it!!!!
Basically I think your question here is answered and I think its time to close it and open a new one. I don't think the last dozen comments are related to your original question, I don't mind helping, but not if you are just going to continue on asking new questions, especially if you don't give me some very basic information.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now