Is it possible to dynamically create a table from as SSIS source

Question

Objective: automate creating a table along with all its columns and data types given a SSIS source

My guess is:

1) Pointing Sources to a Destination to a SQL command

2) Using Select * into ... Problem is I don't know what the from equivalent of a source is

Alternative) Store results in Recordset and pass on to Execute SQL task. Problem then is how to access that result from execute sql task

Answer 1

If you need just the structure of table use this trick

select top 0 * into NewTable from YourTable

Answer 2

I think you should use a Recordset Destination to store data into an System.Object Variable, Then use a Script Task (starts after that Data Flow Task is executed) in which you will select the System.Object Variable as ReadOnly Variable. and you will write your own code to insert the Recordset to SQL using System.Data.SqlClient.SQLCommand Object

You can refer to one of these links

• Issues with SSIS Script Task and SQL Bulk Insert - C#
• Insert DataTable into SQL Table in C#