SSIS: Cannot put a SQL Task within a Foreach Loop

Question

I am new to SSIS and I am relying on a book, so this should be an easy question to answer.

I want to import data from all of the text files within a directory. So I create a Foreach container.

The first thing I want to do within the loop is execute a SQL task to drop and create the landing table.

Then I want to load the data into the landing table from the selected text file using a Data Flow task.

Then I want to exexcute a SQL Task to load the SQL tables from the Landing table and do some calculations.

The problem is that within a Foreach loop, I cannot connect up a SQL Task to a Data Flow task. The message I get is

Cannot create connector. Cannot connect the executables from different containers.

So how do I do this?

Answer 1

It looks like you are trying to connect a Sql Task within a foreach loop to a DataFlow task that is not in the loop. If that is what you are intending, you need to take the connection from the ForEach loop and connect it to the DataFlow. If, however, you want both the SQLTask and the Dataflow to run within the ForEachLoop, you need to put the DataFlow into the ForEach loop and then you will be able to connect the SQLTask to the DataFlow.

Answer 2

How about a screen shot? I've used these 2 components in a loop container before with no problem, my guess is the GUI is a little deceptive here.