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
