Programmatically Creating SSIS Package

Hi guys,
 
I was intended to write a program that will create a SSIS package which will import data from a CSV file to the SQL server 2005. But I did not find any good example for this into the internet. I found some example which exports data from SQL server 2005 to CSV files. And following those examples I have tried to write my own. But I am facing some problem with that. What I am doing here is creating two connection manager objects, one for Flat file and another for OLEDB. And create a data flow task that has two data flow component, one for reading source and another for writing to destination. While debugging I can see that after invoking the ReinitializedMetaData() for the flat file source data flow component, there is not output column found. Why it is not fetching the output columns from the CSV file? And after that when it invokes the ReinitializedMetaData() for the destination data flow component it simply throws exception.
Can any body help me to get around this problem? Even can anyone give me any link where I can find some useful article to accomplish this goal?
I am giving my code here too.
I will appreciate any kind of suggestion on this.
 
Code snippet:
 
public void CreatePackage()
{
string executeSqlTask = typeof(ExecuteSQLTask).AssemblyQualifiedName;
      Package pkg = new Package();
      pkg.PackageType = DTSPackageType.DTSDesigner90;
ConnectionManager oledbConnectionManager =              CreateOLEDBConnection(pkg);
ConnectionManager flatfileConnectionManager =
             CreateFileConnection(pkg);
      // creating the SQL Task for table creation
Executable sqlTaskExecutable = pkg.Executables.Add(executeSqlTask);
ExecuteSQLTask execSqlTask = (sqlTaskExecutable as Microsoft.SqlServer.Dts.Runtime.TaskHost).InnerObject as ExecuteSQLTask;
      execSqlTask.Connection = oledbConnectionManager.Name;           
      execSqlTask.SqlStatementSource =
"CREATE TABLE [MYDATABASE].[dbo].[MYTABLE]
([NAME] NVARCHAR(50),[AGE] NVARCHAR(50),[GENDER] NVARCHAR(50))
GO";
      // creating the Data flow task
Executable dataFlowExecutable = pkg.Executables.Add("DTS.Pipeline.1");
      TaskHost pipeLineTaskHost = (TaskHost)dataFlowExecutable;
      MainPipe dataFlowTask = (MainPipe)pipeLineTaskHost.InnerObject;           
      // Put a precedence constraint between the tasks.           
PrecedenceConstraint pcTasks = pkg.PrecedenceConstraints.Add(sqlTaskExecutable, dataFlowExecutable);
      pcTasks.Value = DTSExecResult.Success;
      pcTasks.EvalOp = DTSPrecedenceEvalOp.Constraint;
      // Now adding the data flow components
IDTSComponentMetaData90 sourceDataFlowComponent = dataFlowTask.ComponentMetaDataCollection.New();
      sourceDataFlowComponent.Name = "Source Data from Flat file";
      // Here is the component class id for flat file source data
      sourceDataFlowComponent.ComponentClassID = "{90C7770B-DE7C-435E-880E-E718C92C0573}";
CManagedComponentWrapper managedInstance = sourceDataFlowComponent.Instantiate();
      managedInstance.ProvideComponentProperties();           
            sourceDataFlowComponent.
RuntimeConnectionCollection[0].ConnectionManagerID = flatfileConnectionManager.ID;
            sourceDataFlowComponent.
RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(flatfileConnectionManager);
     
managedInstance.AcquireConnections(null);
      managedInstance.ReinitializeMetaData();
      managedInstance.ReleaseConnections();
      // Get the destination's default input and virtual input.
IDTSOutput90 output = sourceDataFlowComponent.OutputCollection[0];
                        // Here I dont find any columns at all..why??
      // Now adding the data flow components
IDTSComponentMetaData90 destinationDataFlowComponent = dataFlowTask.ComponentMetaDataCollection.New();
      destinationDataFlowComponent.Name =
"Destination Oledb compoenent";
      // Here is the component class id for Oledvb data
      destinationDataFlowComponent.ComponentClassID = "{E2568105-9550-4F71-A638-B7FE42E66922}";
CManagedComponentWrapper managedOleInstance = destinationDataFlowComponent.Instantiate();
      managedOleInstance.ProvideComponentProperties();
destinationDataFlowComponent.
RuntimeConnectionCollection[0].ConnectionManagerID = oledbConnectionManager.ID;
destinationDataFlowComponent.
RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(oledbConnectionManager);
      // Set the custom properties.
      managedOleInstance.SetComponentProperty("AccessMode", 2);                 
managedOleInstance.SetComponentProperty("OpenRowset", "[MYDATABASE].[dbo].[MYTABLE]");           
      managedOleInstance.AcquireConnections(null);
      managedOleInstance.ReinitializeMetaData();  // Throws exception
      managedOleInstance.ReleaseConnections();           
      // Create the path.
IDTSPath90 path = dataFlowTask.PathCollection.New();            path.AttachPathAndPropagateNotifications(sourceDataFlowComponent.OutputCollection[0],
      destinationDataFlowComponent.InputCollection[0]);
      // Get the destination's default input and virtual input.
IDTSInput90 input = destinationDataFlowComponent.InputCollection[0];
      IDTSVirtualInput90 vInput = input.GetVirtualInput();
      // Iterate through the virtual input column collection.
foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
      {
          managedOleInstance.SetUsageType(
input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
      }
      DTSExecResult res = pkg.Execute();
}
public ConnectionManager CreateOLEDBConnection(Package p)
{
      ConnectionManager ConMgr;
      ConMgr = p.Connections.Add("OLEDB");
      ConMgr.ConnectionString =
"Data Source=VSTS;Initial Catalog=MYDATABASE;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=false;";
      ConMgr.Name = "SSIS Connection Manager for Oledb";
      ConMgr.Description = "OLE DB connection to the Test database.";
      return ConMgr;
}
public ConnectionManager CreateFileConnection(Package p)
{
      ConnectionManager connMgr;
      connMgr = p.Connections.Add("FLATFILE");
      connMgr.ConnectionString = @"D:MyCSVFile.csv";
      connMgr.Name = "SSIS Connection Manager for Files";
      connMgr.Description = "Flat File connection";
      connMgr.Properties["Format"].SetValue(connMgr, "Delimited");
connMgr.Properties["HeaderRowDelimiter"].SetValue(connMgr, Environment.NewLine);
      return connMgr;
}
 
And my CSV files is as follows
 
NAME, AGE, GENDER
Jon,52,MALE
Linda, 26, FEMALE
 
Thats all. Thanks.