Dynamically create table in SSIS data flow

Question

How can I have an SSIS data flow create a table that doesn't exist? Every time I try to run the package, it gives me an error saying that the destination table doesn't exist.

Answer 1

WHy not create the table in the step before the dataflow in an execute SQL task?

Answer 2

Since you want your package to create a table and then populate it, you can create one using the Execute SQL task. Just give it the script to create your table.

If you do not have the option of using an Execute SQL or Script task, then you can use a Script Component, but since it executes for every row, you'll have to get creative to have it execute only once.

Also, when you run this package, SSIS going to crib about the table not being found during Validation.

To counter that, in the package properties, in data-flow that populates the table, set Delay Validation property to True