SSIS conditional Split
I needed to figure out how to get SSIS to consume a file that had lines of data with attributes of both "apples" and "oranges". No matter where I looked online, it was as if SSIS could not take in a file that had different types of data on each line and then throw that data into a database. The situation looked bleak, until I found a little item in my toolbox inside a data flow called "Conditional Split".
Conditional Split is almost like a little tool that makes your flat file like XML in that you can send data from the source to many tables. When you consume an XML file in SSIS, you could have many tables that could flow from that source. The same is true when you use Conditional Split; you tell SSIS when a line is a different row type and BAMM, you are on your way to multiple tables of data getting inserted into your database.
For me, the trick to all of this wasn't in how I accomplished this, but rather how to know what types of tools you need to use in SSIS to do what you need to do.
I have my Data Flow task.
Inside that I have my flat file Source.
That component points to a "Conditional Split" component.
Inside of that component I simply created multiple conditions that meant that if a line started like X, then output that line to a separate flow into a table. In my case, I said substring(Field_name,1,5)=="Batch" and called that Output Name "Batch_Details" table. I created other lines to, like substring(Field_name,1,3)=="Act" with an Output name of "Account Details". You can create as many types of output lines as you might need, although I don't know what the technical limit is, I only needed 10.
That component then pointed to a derived columns component. In my case, I had a fixed length file so I created a bunch of columns that were created by substring commands.
That component pointed to a OLE DB Destination.
That was it. It turned out to be pretty simple; if I could only figure out how SSIS could perform the actions. I remain disappointed that there weren't many posts, FAQ's or much of anything out on the net to help reveal how to perform these actions.
Keith