Understanding the "API Sample - OleDB Source and OleDB Destination" article in SSIS Team blog

SSIS Team Blog has written a good article titled "API Sample - OleDB source and OleDB destination", showing how to programmatically create a data flow that reads from OleDB source and writes to a different Ole DB destination table.

http://blogs.msdn.com/mattm/archive/2008/12/30/api-sample-oledb-source-and-oledb-destination.aspx

The problem I'm running into is around the following couple lines of code:

destDesignTimeComponent.SetComponentProperty("AccessMode", 3);
destDesignTimeComponent.SetComponentProperty("OpenRowset", "[DimCustomer_Copy]")

The above code only works if the table DimCustomer_Copy already exists. What I'm trying to do is have the program learning the structure 开发者_JAVA技巧of the source table and create the destination table automatically. As explained early on in the article, there's an option of passing a value of 2 to the AccessMode property and the sql statement to the SqlCommand property. So what I did is

destDesignTimeComponent.SetComponentProperty("AccessMode", 2);
destDesignTimeComponent.SetComponentProperty("OpenRowset", @"CREATE TABLE ...")

But that doesn't seem to work. I know I can always create a table using SqlCommand, SqlConnection, etc., but I prefer to have the destination table created automatically using SSIS in this programmatic way. So my question is how to do so.

It's because the ValidateExternalMetadata was set to True. However if you set to false, you must implement your own 'disconnected' validation.