I have a couple of different packages that run against the same database and often same tables throughout the day using SQL Server Agent. The packages have a data flow tasks and the data flow task use OLE DB Reader against SQL Server and OLE DB Command transformations to update MS Access. Quite often, the jobs will fail and indicate that the file is locked. See below: Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Could not use ''; file already in use.". End Error Error: 2012-06-08 08:50:05.44 Code: 0xC020801C Source: Process Form Data Update Mileage and Oil Expiration [480] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "dev_tables" failed with error code 0xC0202009. The Transaction Level of the Data Flow Task is set to serializable. The OLE DB connection Mode is 16 and the Jet Database locking mode is 1. It appears that the jobs fail immediately before they ever execute any of the transformations steps in the data flow task. Is there some sort of validation that is occuring prior to the execution that is checking for concurrent access? Is there a way to retry the OLE DB Command transformation if it gets an error? Basically, try to do the update/insert and if it fails because of a lock, try again. Thank you, Steve

Steve, In your agent job, how about you try to create a bew step just above the SSIS package that would be a dummy one? Then in the SSIS package step below, go to the advanced property page and specify on failure to go to that step above, this way you give it a chance to retry PS: You may want to limit the retry attempts to say 3, or so.Arthur My Blog

Hmm. I have been looking at how to retry the OLE DB Command transformation within the task instead of the entire task. Let me take a look at that approach. Thank you.

You can create a for loop in which this OLEDB command will reside, that will be based on a package variable conditional value and if you get an error you set this value say in an Event handler; if the OLEDB command succeeds then it gets another value that makes the loop exiting.Arthur My Blog

So on the error side of the OLE DB Command transformation is the only way to set the variable to use a Script Component?

The error output you mean, if you want to use it only, but it is also possible to use an event handler that is tied to just this particular OLEDB component and I think it is more desirable.Arthur My Blog

The error output you mean, if you want to use it only, but it is also possible to use an event handler that is tied to just this particular OLEDB component and I think it is more desirable.Arthur My Blog

I have it setup to use SQL Server Agent Errors to just go back to the SSIS package execution step. This works but is not ideal because it reruns the entire package. I would like to figure out how to just retry the OLE DB Command Transformation that fails. I looked at event handlers, but it appears you can only set those up on the task level not the data flow transformation level for the OLE DB Command transformation. In my scenarnio, my packages has a single data flow task. That data flow task has several conditional paths that execute different sql statements using the OLE DB Command transformation. I would like to know when on of the OLE DB Command transformation fails because of a database lock and then retry it, doing this as many times as necessary (or limiting it to some number) until it is successfull where it will continue the data flow task execution. Is it possible to use event handlers on a specific transformation step within the data flow task and have it repeat that transformation step?