SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to - RUN MORE than 1 SSIS package at the same time on the SQL Server, using DTExec.exe


How to - RUN MORE than 1 SSIS package at the same time on the SQL Server, using DTExec.exe

Author
Message
ms-techie
ms-techie
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 56
Our SSIS packages are automated to run in production via JOBS which use the DTExec.exe line to invoke the individual packages

We have a total of 25 jobs like this. These Jobs are INDEPENDENT of each other. So the OUTPUT of one Job , does not affect any other jobs.

Some of the jobs are jobs are scheduled to RUN , at the same-time .(Since they dont depend on one another ) and the business wants data at the same time .

Now in this scenario, we get the following error

"The process cannot access the file because it is being used by another process " and then the job fails . This is because DTExec.exe Process is being accessed by many jobs

Is there a way , wherein , 3 jobs can be scheduled to RUN at the same time , without getting the above error. Is there some settings that needs to be done on the Server , where this job runs.

Pls help
Lempster
Lempster
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3225 Visits: 1657
ms-techie (2/23/2014)
These Jobs are INDEPENDENT of each other.

Are you sure about that?

"The process cannot access the file because it is being used by another process " and then the job fails . This is because DTExec.exe Process is being accessed by many jobs
Pls help

I think it unlikely that this is the reason for the error. It's more likely that more than one of your packages are trying to access the same Source or Destination file.

Also, you may get more reponse to your post if you submit it to the correct forum; you have posted in the SSRS forum.

Regards
Lempster
sqlbuddy123
sqlbuddy123
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2392 Visits: 2243
ms-techie (2/23/2014)


Now in this scenario, we get the following error

"The process cannot access the file because it is being used by another process " and then the job fails . This is because DTExec.exe Process is being accessed by many jobs

Pls help


It looks like they are trying to access the same file simultaneous. It might not be dtexec. Or does the error explicitly mentions that ? Could you post the whole error ..

--
SQLBuddy
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27625 Visits: 13268
ms-techie (2/23/2014)
Our SSIS packages are automated to run in production via JOBS which use the DTExec.exe line to invoke the individual packages

We have a total of 25 jobs like this. These Jobs are INDEPENDENT of each other. So the OUTPUT of one Job , does not affect any other jobs.

Some of the jobs are jobs are scheduled to RUN , at the same-time .(Since they dont depend on one another ) and the business wants data at the same time .

Now in this scenario, we get the following error

"The process cannot access the file because it is being used by another process " and then the job fails . This is because DTExec.exe Process is being accessed by many jobs

Is there a way , wherein , 3 jobs can be scheduled to RUN at the same time , without getting the above error. Is there some settings that needs to be done on the Server , where this job runs.

Pls help


You can run a lot of packages at the same time (depends a bit on the number of processors). You don't have to do anything to enable that. Just make sure there are no conflicts at the source/destination, where your error is probably happening.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
ms-techie
ms-techie
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 56
It works and sometimes fails , without any change in the code .

The error message is as below

We inoke the SSIS package via the DTExec.exe


DTExec /File "\\Server1\SSISPackage1.dtsx"
/ConfigFile "\\Server1\MyConfig.dtsConfig" 1>output2.log


The process cannot access the file because it is being used by another process.

When I checked via process explorer if the SSISPackage1.dtsx and MyConfig.dtsConfig , it shows 0 handles , which means to say these files are not used.

When I Kill the DTExec process from the windows task manager on the server1 , it starts working again . What could have gone wrong in this case
sqlbuddy123
sqlbuddy123
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2392 Visits: 2243
ms-techie (3/12/2014)
It works and sometimes fails , without any change in the code .

The error message is as below

We inoke the SSIS package via the DTExec.exe


DTExec /File "\\Server1\SSISPackage1.dtsx"
/ConfigFile "\\Server1\MyConfig.dtsConfig" 1>output2.log


The process cannot access the file because it is being used by another process.

When I checked via process explorer if the SSISPackage1.dtsx and MyConfig.dtsConfig , it shows 0 handles , which means to say these files are not used.

When I Kill the DTExec process from the windows task manager on the server1 , it starts working again . What could have gone wrong in this case


If it works sometimes it means, the solution is correct. Only the timing might be a problem.

Check if there is any input\output file (flatfile, log file ..) that's being shared at the same time ? Or may be someone is opening the file and not closing it which makes the package to fail ..

--
SQLBuddy
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search