No Column Was Specified To Allow The Component To Advance Through The File.

Hello,

I apologize in advance if this seems like a relative easy answer, however, I can't find it anywhere, and I can't figure it out personally, I am relatively new to SSIS and C#.

I am attempting to write in C# a simple program, where I am taking a table from an OLE DB Source and transfering it to a comma delimited flat file.

I have been trying to work through samples and other methods of help, but I continue to get stuck, and this is my latest problem.

When trying to excecute I get the error: No column was sepcified to allow the component to advance through the file, for my flat file destination.

I know what this means, I just do not know how to fix it.


Below is my code so far. I highly doubt my code is the best it can get for this type of example, so if you see anything that I do not need in the code for it to perform what I want it to perform, please let me know.

(The code to excecute this package is in a different file).

using System;

using Microsoft.SqlServer.Dts.Runtime;

using Microsoft.SqlServer.Dts.Pipeline;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

namespace Microsoft.SqlServer.Dts.Samples

{

class Program

{

static void Main(string[] args)

{

// Create a package and add a Data Flow task.

Package package = new Package();

Executable e = package.Executables.Add("DTS.Pipeline.1");

TaskHost thMainPipe = e as TaskHost;

MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;

// Create Application

Application app = new Application();

// Add an OLE DB connection manager to the package.

ConnectionManager conMgr = package.Connections.Add("OLEDB");

conMgr.ConnectionString = "Data Source=ROSIE\ROSIE2005;" +

"Initial Catalog=AdventureWorks;Provider=SQLNCLI;" +

"Integrated Security=SSPI;Auto Translate=false;";

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

conMgr.Description = "OLE DB connection to the " +

"AdventureWorks database.";

// Create and configure an OLE DB source component.

IDTSComponentMetaData90 source =

dataFlowTask.ComponentMetaDataCollection.New();

source.ComponentClassID = "DTSAdapter.OLEDBSource.1";

// Create the design-time instance of the source.

CManagedComponentWrapper srcDesignTime = source.Instantiate();

// The ProvideComponentProperties method creates a default output.

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",

"Select * from HumanResources.EmployeePayHistory");

srcDesignTime.SetComponentProperty("OpenRowset", "[AdventureWorks].[HumanResources].[EmployeePayHistory");

// Need to set the ConnectionManagerID

if (source.RuntimeConnectionCollection.Count > 0)

{

source.RuntimeConnectionCollection[0].ConnectionManagerID =

conMgr.ID;

source.RuntimeConnectionCollection[0].ConnectionManager =

DtsConvert.ToConnectionManager90(conMgr);

}

// Connect to the data source,

// and then update the metadata for the source.

srcDesignTime.AcquireConnections(null);

srcDesignTime.ReinitializeMetaData();

srcDesignTime.ReleaseConnections();

// Add an flat file source connection manager to the package.

ConnectionManager conMgr2 = package.Connections.Add("FlatFile");

conMgr2.ConnectionString = "C:\Documents and Settings\ddoorn" +

"\My Documents\Visual Studio 2005\Projects\" +

"DennisSampleProgram1\EmployeePayHistory.txt";

conMgr2.Name = "SSIS Connection Manager for Flat File";

conMgr2.Description = "Flat File Destination Connection";

// Create Destination Component

IDTSComponentMetaData90 destination =

dataFlowTask.ComponentMetaDataCollection.New();

destination.Name = "Flat File Destination";

destination.ComponentClassID =

"DTSAdapter.FlatFileDestination.1";

CManagedComponentWrapper destDesignTime = destination.Instantiate();

destDesignTime.ProvideComponentProperties();

// Assign the connection manager.

destination.RuntimeConnectionCollection[0].ConnectionManager =

DtsConvert.ToConnectionManager90(conMgr2);

// Set Custom Properties

destDesignTime.SetComponentProperty("Overwrite", true);

// Assignment an ID to the ConnectionManager

if (destination.RuntimeConnectionCollection.Count > 0)

{

destination.RuntimeConnectionCollection[0].ConnectionManagerID =

conMgr2.ID;

destination.RuntimeConnectionCollection[0].ConnectionManager =

DtsConvert.ToConnectionManager90(conMgr2);

}

// Create the path from source to destination.

IDTSPath90 path = dataFlowTask.PathCollection.New();

path.AttachPathAndPropagateNotifications(source.OutputCollection[0],

destination.InputCollection[0]);

// Get the destination's default input and virtual input.

IDTSInput90 input = destination.InputCollection[0];

IDTSVirtualInput90 vInput = input.GetVirtualInput();

// Iterate through the virtual input column collection.

foreach (IDTSVirtualInputColumn90 vColumn

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);

}

//map external metadata to the inputcolumn

//int index = 0;

foreach (IDTSInputColumn90 inputColumn in input.InputColumnCollection)

{

IDTSExternalMetadataColumn90 exMetaColumn =

input.ExternalMetadataColumnCollection.New();

//(IDTSExternalMetadataColumn90)input.ExternalMetadataColumnCollection[index++];



exMetaColumn.CodePage = inputColumn.CodePage;

exMetaColumn.DataType = inputColumn.DataType;

exMetaColumn.Length = inputColumn.Length;

exMetaColumn.Name = inputColumn.Name;

inputColumn.ExternalMetadataColumnID = exMetaColumn.ID;

destDesignTime.MapInputColumn(input.ID, inputColumn.ID, exMetaColumn.ID);

}

// Verify that the columns have been added to the input.

// This is only really required for debugging purposes

Console.WriteLine("Below are the columns that have been added " +

"to the input. Press Enter to Verify");

foreach (IDTSInputColumn90 inputColumn in

destination.InputCollection[0].InputColumnCollection)

{

Console.WriteLine(inputColumn.Name);

}

Console.Read();

// Connect to the data source,

// and then update the metadata for the source.

destDesignTime.AcquireConnections(null);

destDesignTime.ReinitializeMetaData();

destDesignTime.ReleaseConnections();

// Save Package to XML

app.SaveToXml("C:\Documents and Settings\ddoorn\My Documents\" +

"Visual Studio 2005\Projects\DennisSampleProgram1\" +

"DennisSampleProgram1\DennisSampleProject1.xml",

package, null);

} // main

} // program

} // namespace