How to create an excel connection manager programmatically ?

I am following the samples in the online books. I've got an OLEDB connection manager and source component defined. I now want to create an Excel connection manager and destination component (see below). How can I do this?

Dim conMgr As ConnectionManager = package.Connections.Add("OLEDB")

conMgr.ConnectionString = "Provider=SQLOLEDB.1;" "Data Source=FLL-EIS;Initial Catalog=DataLoad;" & _

"Integrated Security=SSPI;"

conMgr.Name = "SSIS Connection Manager for OLE DB"

conMgr.Description = "OLE DB connection to FLL-EIS."

' Create and configure an OLE DB source component.

Dim source As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New

source.ComponentClassID = "DTSAdapter.OleDbSource"

' Create the design-time instance of the source.

Dim srcDesignTime As CManagedComponentWrapper = source.Instantiate

srcDesignTime.ProvideComponentProperties()

' Assign the connection manager.

source.RuntimeConnectionCollection(0).ConnectionManager = _

DtsConvert.ToConnectionManager90(conMgr)

' Set the custom properties of the source.

srcDesignTime.SetComponentProperty("AccessMode", 2)

srcDesignTime.SetComponentProperty("SqlCommand", Dts.Variables("User::gsQuery").Value.ToString)

' Connect to the data source,

' and then update the metadata for the source.

srcDesignTime.AcquireConnections(Nothing)

srcDesignTime.ReinitializeMetaData()

srcDesignTime.ReleaseConnections()

' Create and configure an OLE DB destination. (This is where I need help, as this code does not work)

Dim destination As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New

destination.ComponentClassID = "DTSAdapter.ExcelDestination"

' Create the design-time instance of the destination.

Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate

' Where can I find documentation about the various ComponentClassIds and Properties?

destDesignTime.ProvideComponentProperties()

destDesignTime.SetComponentProperty("ExcelFilePath", Dts.Variables("User::gsExcelFile").Value.ToString)

destDesignTime.SetComponentProperty("TableName", Dts.Variables("User::gsSheetName").Value.ToString)

Here are a couple of wrapper functions I have for creating connections, including an Excel connection-

Code Snippet

private static ConnectionManager AddExcelConnection(Package package, string filename)

{

return AddConnection(package, "EXCEL", String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES\"", filename));

}

private static ConnectionManager AddSqlConnection(Package package, string server, string database)

{

return AddConnection(package, "OLEDB", String.Format("Provider=SQLOLEDB.1;Data Source={0};Persist Security Info=False;Initial Catalog={1};Integrated Security=SSPI;", server, database));

}

private static ConnectionManager AddConnection(Package package, string type, string connectionString)

{

ConnectionManager manager = package.Connections.Add(type);

manager.ConnectionString = connectionString;

manager.Name = String.Format("{0} Connection", type);

return manager;

}

Call it like this -

Code Snippet

Package package = new Package();

// Add the Excel connection

ConnectionManager excelConnection = AddExcelConnection(package, @."C:\Temp\Export.xls");

Is your destination code correct? I the properties look wrong, I always used -

componentInstance.SetComponentProperty("AccessMode", 2);

componentInstance.SetComponentProperty("SqlCommand", query);

|||

Thanks for the info, Darren. So, now I have the following code, but how do I specify the destination table (sheet) name?

Dim conDest As ConnectionManager = package.Connections.Add("Excel")

conDest.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _

Dts.Variables("User::gsExcelFile").Value.ToString & ";Extended Properties=""Excel 8.0;HDR=YES"""

|||

Code Snippet

componentInstance.SetComponentProperty("AccessMode", 0);

componentInstance.SetComponentProperty("OpenRowset", sheetName);

I was wrong above, it is OpenRowset for teh table/sheet not SqlCommand.

|||

Thanks for the reply, Darren. The first SetComponentProperty worked, but not the second, which gets a runtime exception. Is the property under a different name? Thanks for your help!

' Create and configure an OLE DB destination.

Dim conDest As ConnectionManager = package.Connections.Add("Excel")

conDest.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _

Dts.Variables("User::gsExcelFile").Value.ToString & ";Extended Properties=""Excel 8.0;HDR=YES"""

conDest.Name = "Excel File"

conDest.Description = "Excel File"

Dim destination As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New

destination.ComponentClassID = "DTSAdapter.ExcelDestination"

' Create the design-time instance of the destination.

Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate

' The ProvideComponentProperties method creates a default input.

destDesignTime.ProvideComponentProperties()

destination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(conDest)

destDesignTime.SetComponentProperty("AccessMode", 0)

'runtime Exception here

destDesignTime.SetComponentProperty("OpenRowSet", "functions")

|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2175638&SiteID=1