As a follow up to my previous post on how one goes about importing data from a flat file data source into a SQL table and being able to have some control over the name of target table it is worth noting that there’s no option available via the BDIS design surface (that I can see) to allow you to drop the table if it exists and create it again (if the data source is a flat file data source and the target is an ADO.NET data source, this option would appear to be available in the case of copying SQL tables).

In this case what you have to do is switch to the “Control Flow” “tab”, drag and drop an “Execute SQL Task” control flow item onto the design surface and link it to your data flow control as the “parent” or preceding control (fig. #1).

Add "Execute SQL Task" to the BDIS Design Surface

You then specify the necessary T-SQL to create the destination table, dropping the existing table if it exists first, via the Execute SQL Task control’s properties (fig. #2).

Enter T-SQL command(s) to "Execute T-SQL Statement" Properties