Creating a package with data flow task programmatically

I have created a simple package with 1 data flow task:
Source: data reader (to sql mobile)
Destination: oledb (to sql server)
And the aim is to do a "Select * from table_name" from source and move everything to destination database.

I have followed the steps in documentation, but the package fails! An exception is thrown with message: "Exception from HRESULT: 0xC0202072"

Since its the basic functionality of SSIS that I am implementing, it should work! I have spent 2 whole days trying to make this work and now its high time to ask for help. Can you please have a look!

Thanks,
Pragya

P.S. Any pointers to samples implementing a data flow task programmatically will help too!

Code:

Package package = new Package();
MainPipe dataFlow = ((TaskHost)package.Executables.Add("DTS.Pipeline")).InnerObject as MainPipe;

//Add a SQL Mobile connection manager that is used by the component to the package.
ConnectionManager cm = package.Connections.Add("SQLMOBILE");
cm.Name = "SQL Mobile ConnectionManager";
cm.ConnectionString = "Data Source =D:\\Program Files\\Microsoft Visual Studio 8\\SmartDevices\\SDK\\SQL Server\\Mobile\\v3.0\\Northwind.sdf";

//Add a SQL Server connection manager that will be used later.
ConnectionManager cm1 = package.Connections.Add("OLEDB");
cm1.Name = "SQL Server Connection Manager";
cm1.ConnectionString = "Data Source=SERVERNAME;Initial Catalog=tempdb;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;";

//Adding source component for SQL Mobile.
IDTSComponentMetaData90 component = dataFlow.ComponentMetaDataCollection.New();
component.Name = "ADONETSource";
component.ComponentClassID = "Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter, Microsoft.SqlServer.ADONETSrc, Version=9.0.242.0, Culture=neutral, blicKeyToken=89845dcd8080cc91";
CManagedComponentWrapper instance = component.Instantiate();
instance.ProvideComponentProperties();
if (component.RuntimeConnectionCollection.Count > 0)
{
component.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[0]);
}
instance.SetComponentProperty("SqlCommand", "Select * from Employees");

// Reinitialize the metadata.
instance.AcquireConnections(null);
instance.ReinitializeMetaData();
instance.ReleaseConnections();

// Adding destination component for SQL Server
IDTSComponentMetaData90 component1 = dataFlow.ComponentMetaDataCollection.New();
component1.Name = "SQL Server Destination";
component1.ComponentClassID = "DTSAdapter.SqlServerDestination.1";
CManagedComponentWrapper instance1 = component1.Instantiate();
instance1.ProvideComponentProperties();
if (component1.RuntimeConnectionCollection.Count > 0)
{
component1.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[1]);
}
instance1.SetComponentProperty("BulkInsertKeepIdentity", true);
instance1.SetComponentProperty("BulkInsertKeepNulls", true);

//set path between components
IDTSPath90 path = dataFlow.PathCollection.New();
path.AttachPathAndPropagateNotifications(component.OutputCollection[0], component1.InputCollection[0]); //Assuming this is correct

// WORKS FINE TILL HERE -

// Reinitialize the metadata.
instance1.AcquireConnections(null);
instance1.ReinitializeMetaData(); //Throws exception. Message: "Exception from HRESULT: 0xC0202072" . Even if I reinitialize metadata after iterating through inputs of the component, the same exception is thrown at this statement.
instance1.ReleaseConnections();

// Iterate through the inputs of the component.
foreach (IDTSInput90 input in component1.InputCollection)
{
// Get the virtual input column collection for the input.
IDTSVirtualInput90 vInput = input.GetVirtualInput();

// Iterate through the virtual column collection.
foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
{
// Call the SetUsageType method of the design time instance of the component.
instance1.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
}
}

Well, 2 things I notice just from a first look is:

1. You don't set the ConnectionManagerID just the ConnectionManager, but both need to be set.

2. You don't specify a table or sqlstatement for your destination so how does the destination know what table to reinitialize its metadata for?

HTH,
Matt|||1. ConnectionManager.Id is a read only property. How to set it?
2. How can I do that?

Thanks|||

Ok! I got through one step. I added the following line:

instance1.SetComponentProperty("BulkInsertTableName","[Employees]");

to solve this.

However I have related questions,

1. Now the metadata can be initialized, but no rows are being transferred. Something is still amiss...

2. I need to manually create the employees table currently. How can i code the 'create table/view' option so that the table can be generated via code? Do I need to first compile the create table statement with column type information and then execute it using sql client? Is there a better way?

Thanks!

|||

Any information on this would help.

Thanks