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 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.
