Try using the below code to initiate connection (acquire connection) to excel. This code is in C# .net and for SQL server 2005
//-----------Data Flow Inner component starts----------------
MainPipe dataFlowTask = dataFlowTaskHost.InnerObject as MainPipe;
// Source OLE DB connection manager to the package.
ConnectionManager SconMgr = package.Connections["Excel Connection Manager"];
// Create and configure an Excel source component.
IDTSComponentMetaData90 source = dataFlowTask.ComponentMetaDataCollection.New();
source.ComponentClassID = "DTSAdapter.ExcelSource.1";
// Create the design-time instance of the source.
CManagedComponentWrapper srcDesignTime = source.Instantiate();
// The ProvideComponentProperties method creates a default output.
srcDesignTime.ProvideComponentProperties();
source.Name = "Excel Source (Sales)";
// Assign the connection manager.
source.RuntimeConnectionCollection[0].ConnectionManagerID = SconMgr.ID;
source.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(SconMgr);
// Set the custom properties of the source.
srcDesignTime.SetComponentProperty("AccessMode", 0); // Mode 0 : OpenRowset / Table - View
srcDesignTime.SetComponentProperty("OpenRowset", "Sheet1$");
// Connect to the data source, and then update the metadata for the source.
srcDesignTime.AcquireConnections(null);
srcDesignTime.ReinitializeMetaData();
srcDesignTime.ReleaseConnections();
///////////Rest of your dataflow components codes//////
This is perfectly working in my case,
Copy the whole code from this location :
http://www.sqllion.com/2009/06/programming-foreach-loop-container-%E2%80%93-enumerating-excel-files/
Guess it will be sorted this time.
