Inconsistent OLE DB Failure when running SSIS packages

I have a series of SSIS packages which populate 12 different databases. Which data source & target they use is controlled by values passed down from the SQL Agent Scheduler Job Step using "Set Values"

These values are passed to Variables which are used in the Expressions in the connection manager for the database to change the connection string and initial catalog.

e.g. REPLACE( @[User::ConString] , "Royalty", @[User::RoyDb] )

These jobs run successfully the majority of the time but each day I get a significant number of failures where one or more target or error trapping table cant be found. They are all using the same connection manager and most of the tables in the database get updated correctly but the job fails on trying to access one or two of the tables, with the following message in the On Error event:

-1071636248,0x,Opening a rowset for "[dbo].[RIGDUE_DAY]" failed. Check that the object exists in the database.

This happens both when I schedule the jobs in parallel with other jobs running the same packages & when I run the job by itself using the right click, start at step option. e.g.

I had one fail last night, I ran it by itself this morning, it failed, I ran it again, it succeeded. Nothing concerning the data it was transforming had changed.

I have applied the hotfix to service pack one concerning the paralel use of variables in a package as referred to in the following link.

http://support.microsoft.com/kb/918091#appliesto

Any ideas welcome

I am having the same problem. I am connecting to a DB2 source and a SQL Server 2005 destination. I have 5 concurrent dataflow tasks running, 4 of those with only one table and a large amount of data, and the 5th with 22 tables with a small amount of data. All data flow tasks were created from Import Export Wizard and were copied into a single package. There is only 1 source and 1 destination connection manager. The data flow task that almost always fails is the one with 22 tables, but it has succeeded a couple times. Only two of the other data flow tasks have failed and they only fail rarely, but they only fail when the data flow with 22 tables fails.

All source components have AlwaysUseDefaultCodePage = true. Has anyone found the root cause of this problem

Hi for other reasons I had to divide my packages up using seqence containers, since I did this there has been no recurrence of the problem. So the task which sets up my dynamic variables is in a seperate container from that which contains the tasks using the connections.

Not 100% sure why this works but it seems to.

Hope this helps