Execute Data Flow Task based on condition in SSIS

Question

I am very new to SSIS and I am building a package which will import files. I have a Execute SQL Task which will execute a SQL statement which will return files that needs to be imported, but the files will need to go into different tables depending on which file is being imported.

I have 2 Data Flow Tasks in my foreach container but now based on the file type it needs to execute 1 of them

How can I achieve this?

enter image description here

EDIT

This is the query that will be executed:

SELECT ReconMedicalAidFile.fReconMedicalAidFileID,   ReconMedicalAidFile.fReconMedicalAidID, 
ReconMedicalAids.fMedicalAidID, ReconMedicalAidFile.fFileName, 
ReconMedicalAidFile.fFileLocation, ReconMedicalAidFile.fFileImportedDate, ReconMedicalAidFile.fNumberRecords, ReconMedicalAidFile.fUser, 
ReconMedicalAidFile.fIsImported
FROM ReconMedicalAidFile INNER JOIN
ReconMedicalAids ON ReconMedicalAidFile.fReconMedicalAidID = ReconMedicalAids.fReconMedicalAidID
WHERE (fIsImported = 0) AND (fNumberRecords = 0)

So the condition is based on fMedicalAidID

I assume you have multiple files, hence the ForEach loop. How are the file types recognized? By the extension? — Raj
– Raj, Commented Jun 17, 2015 at 8:12
I have a flag column in my table which will indicate which file is which — Gericke
– Gericke, Commented Jun 17, 2015 at 8:15

Eric Hauenstein · Accepted Answer · 2015-06-17 12:55:32Z

4

The dummy script approach: DummyScript

In the expressions for the two precedence constraints you evaluate a variable that is set either by the first SQL task or the script task. enter image description here

Now, the data will flow down one of the two data flow paths based on the value of the variable. The script task can be empty, or it can perform the logic needed to set the variable.

answered Jun 17, 2015 at 12:55

Eric Hauenstein

2,5656 gold badges35 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Raj · Accepted Answer · 2015-06-17 08:47:39Z

1

I don't believe you will be able to do what you want in SSIS with the way your current package is designed.

An alternate approach:

Add a Conditional Split task after the first Execute SQL task
Split based on file type
Create a separate ForEach Loop and Execute SQL task for each file type

answered Jun 17, 2015 at 8:47

Raj

10.9k2 gold badges47 silver badges53 bronze badges

5 Comments

Gericke Over a year ago

Unable to add Conditional Split in the Control Flow, it can only be added in the Data Flow.

Gericke Over a year ago

Is it perhaps possible to set a variable in the foreach that gets passed to the Data Flow and from there to do the split?

Raj Over a year ago

What are you doing with the result set from the first Execute SQL task?

Gericke Over a year ago

I set it to a object type variable and that is where it should iterate through the results

Eric Hauenstein Over a year ago

You can use a dummy script task in place of a conditional split. Just use two outputs with expressions in the constraints.

Collectives™ on Stack Overflow

Execute Data Flow Task based on condition in SSIS

2 Answers 2

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related