Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to - RUN MORE than 1 SSIS package at the same time on the SQL Server, using DTExec.exe Expand / Collapse
Author
Message
Posted Sunday, February 23, 2014 11:34 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 5:10 AM
Points: 25, 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
Post #1544361
Posted Monday, February 24, 2014 4:01 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 22, 2014 4:18 AM
Points: 2,048, Visits: 1,403
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
Post #1544415
Posted Tuesday, March 4, 2014 11:56 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 11:19 AM
Points: 1,194, Visits: 2,215
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
Post #1547480
Posted Tuesday, March 4, 2014 2:18 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:16 PM
Points: 13,616, Visits: 10,505
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1547561
Posted Wednesday, March 12, 2014 6:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 5:10 AM
Points: 25, 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
Post #1550186
Posted Wednesday, March 12, 2014 10:17 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 11:19 AM
Points: 1,194, Visits: 2,215
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
Post #1550338
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse