FeedFile Excel Connection Manager Concurrency Issue?

Hi,

RE. SSIS (v9.00.1399.00) FeedFile Excel Connection Manager Concurrency Issue

1. I have 15 completely separate SSIS Packages all of which use the FeedFile Excel Connection Manager to read data from completely separate Excel worksheets.

2. When I invoke, say, 5 of these Packages at the same time then one or more (at random) of them fail with the following error message:-

The AcquireConnection method call to the connection manager "FeedFile Excel Connection Manager" failed with error code 0xC0202009.

3. When I invoke the failed Packages again then they succeed.

So, do you know if there is:-

(a) a concurrency issue with the "FeedFile Excel Connection Manager" ?

(b) a read-only option (in the SSIS Package code) which needs to be specified to overcome file contention issues?

Additional Information about the FeedFile Excel Connection Manager:-

Connection String=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DoesNotNeedToExist.xls;Extended Properties="EXCEL 8.0;HDR=NO";

DelayValidation=True

ExcelFilePath= C:\DoesNotNeedToExist

RetainSameConnection=False

Note1. The actual Excel filename which is read is generated dynamically at run-time by a Script Task. This is why DelayValidation has been set to: True (Note. The file: "C:\DoesNotNeedToExist" does not actually physically exist.)

Note2. We have another SSIS Package which uses the FeedFile FlatFile Connection Manager. When we run this same Package concurrently (i.e. invoke it 5 times, at the same time, with different input files) then all 5 invocations succeed, which is what we would expect.

Thanks.

What is the "FeedFile Excel Connection Manager" as opposed to the regular Excel connection?

Is there no more detail? If running in BIDS, check the output winbdow, or if running unattanded, turn on logging.

I would expect there to be some additional error messages that go with 0xC0202009 error, which give the reason for this generic failure report.

|||

Sorry Darren, ignore the prefix FeedFile, it is the regular Excel connection.

I have not had a chance to look at this problem again but I will return to it later this week. I will let you know how I get on with trying to reproduce it in BIDevStudio etc.

From what I can gather though, there should be no limit to the number of separate SSIS Packages that can be run concurrently on a Server. Is this correct?

Fast Uploading Data from Client to SQL2000 Database

Does anyone know how to upload (bulk) data from a client (written in Excel VBA) to a remote SQL2000 database? Of coarse I tried "INSERT INTO" and rst.addnew but I noticed this is much, much slower as downloading from the same remote database.
Thanks.It tends to be faster if you can do this the other way round i.e. get SQL Server to load it from Excel rather than using Excel to load it into SQL Server.|||You can try to use BCP function or SQL DTS to import into the relevant table. This will automate your process and make it much faster. Sometimes, it depend on the indexing,size of a table or the design of the database for performance.|||BCP uses the filesystem which I cannot access from the client v.v.