I am creating and running a package programmatically. I have the source component set up fine, and the destination component seems good, but when the package is run, it gets the error message: "Excel destination failed validation and returned validation status "VS_NEEDSNEWMETADATA" ". This would lead me to believe that I need a ReinitializeMetaData() call, but I already have that (see below). How do I fix this? 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).ConnectionManagerID = conDest.ID
destination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(conDest)
destDesignTime.SetComponentProperty("AccessMode", 0)
destDesignTime.SetComponentProperty("OpenRowset", Dts.Variables("User::gsSheetName").Value.ToString)
destDesignTime.AcquireConnections(Nothing)
destDesignTime.ReinitializeMetaData()
destDesignTime.ReleaseConnections()
' Create the path from source to destination.
Dim path As IDTSPath90 = dataFlowTask.PathCollection.New
path.AttachPathAndPropagateNotifications(source.OutputCollection(0), _
destination.InputCollection(0))
' Get the destination's default input and virtual input.
Dim input As IDTSInput90 = destination.InputCollection(0)
Dim vInput As IDTSVirtualInput90 = input.GetVirtualInput
' Iterate through the virtual input column collection.
For Each vColumn As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection
' Call the SetUsageType method of the destination
' to add each available virtual input column as an input column.
destDesignTime.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY)
Next
You have already called ReinitializeMetaData, but for a destination it should be done after you have connected the input. ReinitializeMetaData for destination is all about creating input columns and mapping them to external columns. If you have no input, then it will no do much. Subsequently I woudl expect validate to fail saying to call RMD again, as no columns is ivalid in my book.|||
I just tried moving the ReinitializeMetaData call to after the SetUsageType loop. There is no validation error now, but the package still fails. It appears that the source output was not mapped to the dest input. I get warning messages that
"the source columns are not subsequently being used in the data flow", and also an error message on the destination that "the number of columns is incorrect", and
"Cannot create OLEDB accessor. Verify that the column metadata is valid.", and finally a return error code of 0xc0202025
Is there something else I am missing, or something else in my code that is incorrect, out of order? Thanks...
|||
Have a think about the process required, and what each method does. For a destination, it is usual that you have an input and some external metadata columns. You need to first select columns. This makes them into input columns, and available to the component. You select columns from the virtual input, which represents all columns potentially available. Once you have input columns, you map them to the external metadata columns. These will have been generated during ReinitializeMetaData, and represent the external destination itself.
If you have a look at components with the Advanced Editor, things like how inputs, outputs and external columns are all used, and related.
Put simply, you need to select colums (from the virtual input) and then map what are now input columns, to the external columns. Here is a snippet from MS supplied CreatePackage sample. You can download new and updated samples from MS Downloads.
Code Snippet
#region MapFlatFileDestination Columns
private void MapFlatFileDestinationColumns()
{
CManagedComponentWrapper wrp = this.flatfileDestination.Instantiate();
IDTSVirtualInput90 vInput = this.flatfileDestination.InputCollection[0].GetVirtualInput();
foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
{
wrp.SetUsageType(this.flatfileDestination.InputCollection[0].ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
}
// For each column in the input collection
// find the corresponding external metadata column.
foreach (IDTSInputColumn90 col in this.flatfileDestination.InputCollection[0].InputColumnCollection)
{
IDTSExternalMetadataColumn90 exCol = this.flatfileDestination.InputCollection[0].ExternalMetadataColumnCollection[col.Name];
wrp.MapInputColumn(this.flatfileDestination.InputCollection[0].ID, col.ID, exCol.ID);
}
}
#endregion
|||
Thanks for the info, Darren. Sorry if I seem a little dense, I didn't see any example of MapInputColumn in the BOL, so that was new to me
I still have a problem, though. If you look at my code below, you'll see that I added the MapInputColumn to the end. However, when I run it, the destination.InputCollection(0).InputColumnCollection set is empty. I specified an existing file with a table defined. The SetUsageType works, so there are the correct number of columns (4) in the VirtualInputColumnCollection. Again, sorry to have so many questions, but we're really close on this one and I just want to solve it.
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).ConnectionManagerID = conDest.ID
destination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(conDest)
destDesignTime.SetComponentProperty("AccessMode", 0)
destDesignTime.SetComponentProperty("OpenRowset", Dts.Variables("User::gsSheetName").Value.ToString)
' Create the path from source to destination.
Dim path As IDTSPath90 = dataFlowTask.PathCollection.New
path.AttachPathAndPropagateNotifications(source.OutputCollection(0), _
destination.InputCollection(0))
' Get the destination's default input and virtual input.
Dim input As IDTSInput90 = destination.InputCollection(0)
Dim vInput As IDTSVirtualInput90 = input.GetVirtualInput
MsgBox(input.InputColumnCollection.Count)
' Iterate through the virtual input column collection.
For Each vColumn As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection
' Call the SetUsageType method of the destination
' to add each available virtual input column as an input column.
destDesignTime.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY)
Next
destDesignTime.AcquireConnections(Nothing)
destDesignTime.ReinitializeMetaData()
destDesignTime.ReleaseConnections()
Dim exCol As IDTSExternalMetadataColumn90
For Each column As IDTSInputColumn90 In input.InputColumnCollection
exCol = destination.InputCollection(0).ExternalMetadataColumnCollection(column.Name)
destDesignTime.MapInputColumn(destination.InputCollection(0).ID, column.ID, exCol.ID)
Next
|||
I am creating/running a package programmatically. I have the source component defined, and am trying to map the destination's input columns. The problem is that the destination.InputCollection(0).InputColumnCollection is empty, even though I have specified a destination Excel file that exists and has the specified table. The VirtualInputColumnCollection does have the correct columns, so I don't know why the InpuColumnCollection would be empty.
Am I missing a step in my code, or is there a way to create the columns manually? Thanks for your help.
Code Snippet
' 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).ConnectionManagerID = conDest.ID
destination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(conDest)
destDesignTime.SetComponentProperty("AccessMode", 0)
destDesignTime.SetComponentProperty("OpenRowset", Dts.Variables("User::gsSheetName").Value.ToString)
' Create the path from source to destination.
Dim path As IDTSPath90 = dataFlowTask.PathCollection.New
path.AttachPathAndPropagateNotifications(source.OutputCollection(0), _
destination.InputCollection(0))
' Get the destination's default input and virtual input.
Dim input As IDTSInput90 = destination.InputCollection(0)
Dim vInput As IDTSVirtualInput90 = input.GetVirtualInput
' Iterate through the virtual input column collection.
For Each vColumn As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection
' to add each available virtual input column as an input column.
destDesignTime.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY)
Next
destDesignTime.AcquireConnections(Nothing)
destDesignTime.ReinitializeMetaData()
destDesignTime.ReleaseConnections()
Dim exCol As IDTSExternalMetadataColumn90
' This for loop does not get executed because the collection is empty
For Each column As IDTSInputColumn90 In destination.InputCollection(0).InputColumnCollection
exCol = destination.InputCollection(0).ExternalMetadataColumnCollection(column.Name)
destDesignTime.MapInputColumn(destination.InputCollection(0).ID, column.ID, exCol.ID)
Next
app.SaveToXml("c:\newpackage.dtsx", package, Nothing)
Dim ret As DTSExecResult
ret = package.Execute()
Console.WriteLine(ret.ToString)
MsgBox(ret.ToString)
|||
Hi
Have a look at this post where i had a similar problem.
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2181121&SiteID=17
Try to delegate the input column generation to the destination component.
Manuel Bauer
|||
I'll guess that you should call RMD before you select the columns. The SetUsageType method selects the column, which makes it avilable in the Input Column Collection.
I have written a complete sample. It assumes you have a workbook in the write location, with a sheet. We will leave creating workbooks and sheets for another day. Assuming the file is there and you have a local SQL server default instance, it will work I assure you! Pick what you need -
Code Block
namespace SSISExcelExport
{
class SimplePackage
{
public void CreatePackage()
{
Package package = new Package();
// Add the SQL connection
ConnectionManager sqlConnection = AddSqlConnection(package, "localhost", "master");
// Add the Excel connection
ConnectionManager excelConnection = AddExcelConnection(package, @."C:\Temp\Export.xls");
// Add the Data Flow task
package.Executables.Add("DTS.Pipeline.1");
// Get the pipeline
TaskHost dataFlowTask = package.Executables[0] as TaskHost;
MainPipe pipeline = dataFlowTask.InnerObject as MainPipe;
// Add the SQL Server source
string query = "SELECT id, name FROM sysobjects";
IDTSComponentMetaData90 source = pipeline.ComponentMetaDataCollection.New();
source.ComponentClassID = "DTSAdapter.OleDbSource.1";
source.Name = "SQL Source";
CManagedComponentWrapper sourceInstance = source.Instantiate();
sourceInstance.ProvideComponentProperties();
source.RuntimeConnectionCollection[0].ConnectionManagerID = sqlConnection.ID;
source.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(sqlConnection);
sourceInstance.SetComponentProperty("AccessMode", 2);
sourceInstance.SetComponentProperty("SqlCommand", query);
sourceInstance.AcquireConnections(null);
sourceInstance.ReinitializeMetaData();
sourceInstance.ReleaseConnections();
// Add Excel destination
string sheetName = "Sheet";
IDTSComponentMetaData90 destination = pipeline.ComponentMetaDataCollection.New();
destination.ComponentClassID = "DTSAdapter.ExcelDestination.1";
destination.Name = "Excel Destination";
CManagedComponentWrapper destinationInstance = destination.Instantiate();
destinationInstance.ProvideComponentProperties();
destination.RuntimeConnectionCollection[0].ConnectionManagerID = excelConnection.ID;
destination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(excelConnection);
destinationInstance.SetComponentProperty("AccessMode", 0);
destinationInstance.SetComponentProperty("OpenRowset", sheetName);
destinationInstance.AcquireConnections(null);
destinationInstance.ReinitializeMetaData();
destinationInstance.ReleaseConnections();
IDTSInput90 destinationInput = destination.InputCollection[0];
// Connect the source to the destination
IDTSPath90 path = pipeline.PathCollection.New();
path.AttachPathAndPropagateNotifications(source.OutputCollection[0], destinationInput);
// Select destination input columns
IDTSVirtualInput90 virtualInput = destinationInput.GetVirtualInput();
foreach (IDTSVirtualInputColumn90 column in virtualInput.VirtualInputColumnCollection)
{
destinationInstance.SetUsageType(destinationInput.ID, virtualInput, column.LineageID, DTSUsageType.UT_READONLY);
}
// Map input column to external metadata column.
for (int index = 0; index < destinationInput.InputColumnCollection.Count; index++)
{
destinationInstance.MapInputColumn(destinationInput.ID, destinationInput.InputColumnCollection[index].ID, destinationInput.ExternalMetadataColumnCollection[index].ID);
}
#if DEBUG
// Save package to disk, DEBUG only
new Application().SaveToXml(@."C:\Temp\" + package.Name, package, null);
#endif
package.Execute();
}
#region Add Connections
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;
}
#endregion
}
}
