SSIS Dataflow - conditional split

Question

SSIS

Hello.

Firstly excuse me if this doesn’t make a lot of sense as I’m fairly (as in 2 days ago) new to SSIS.

Ultimately I’m looking to import all data from an XML file and depending on what kind of file it is process in a different way and load to a set of relational tables in a Microsoft SQL Server 2008.

Each XML file has a Boolean flag of 0 or 1. Depending on if its 0 or 1 I would like the data flow to go a different way as each type of file needs to be processed differently.

I’ve been looking at using the conditional split transformation however I’ve come across a couple of issues.

I get the data using a dataflow XML source and check the flag then depending on if its 0 or 1 the data flows left or right. However the XML contains lots of data and I can only move the data element with the flag and I need it to move the rest of the data in that direction as well.

I thought I might open the file check the flag, store the flag as a variable then close the file and open a 0 package or a 1 package depending on the variable which would then open the xml file again and pull out the data. However this doesn’t “smell” right as I’m opening and closing the same XML file twice.

Has anyone else come across this and how did they get around it?

Answer 1

Although I don't think anything wrong with opening a file twice in an ETL operation, you can achieve your goal with a single data flow task that includes a conditional split component which uses a variable (or derived column etc) for condition expression. Setting the variable can be easily implemented in data flow task by using a script component.