Hello Firends, I got a task where i have a flat file as my source. The flat file consists of three different types of data . For example: 1, alex, anderson, Maths 1, Maths, 08-11-2011 1, 010 This is how the data is in the flat file. I want to send this data into three different tables using 3 destinations... like : 1, alex, anderson, Maths should go into Table-1 1, Maths, 08-11-2011 should go into Table-2 1, 010 should go into Table-3 Can any one help me out If it is possible how can you do this??? Thanks a ton, Maverick.

Hello Maverick, you can set your Flat File connection to recognize your input file as a single column data file. Then you shall use the Conditional Split component to accomplish this redirection. In the CS Task you break up the oncoming string into three chunks based on some logic that I see at the moment as could be based on the number of commas (so you need the proper SSIS Expression for that). Then, once the data stream from the file is broken into chunks you simply direct it say to yet another file which you load as needed or you can say use another conditional split (CS) to feed a Data Flow that loads it into a table. See an example on how to do that more or less close to what you need: http://www.techrepublic.com/blog/datacenter/handle-conditional-data-and-data-conversion-using-sql-server-2005-integration-services/212 or http://www.codeproject.com/KB/database/SSIS_ConditionalSplit.aspxArthur My Blog

Use a data flow task, inside the data flow task, add a flat file source, in the flat file connection manager, in the columns tab, delete the ColumnDelimiter value, and click on refresh, then all column values will be concatenate in one column, now in advanced tab, you can set an appropriate length for this single column, then add a conditional split transformation, and write expressions to split rows based on number of comma in each rows, this can be expressions there: Order Output Name Condition ------------------------------------------------------------------------------------------------ 1 output for table1 FINDSTRING([Column 0],",",3) > 0 2 output for table2 FINDSTRING([Column 0],",",2) > 0 3 output for table3 FINDSTRING([Column 0],",",1) > 0 then add three derived column transformations, after the conditional split ( these transformations should split the column content to more columns per each comma) connect the output for table1 from conditional split to derived column transform 1, and inside the derived column write these expressions to fetch columns: Derived Column Name Expression ---------------------------------------------------------------------------------------- derived column 1 SUBSTRING([Column 0],1,FINDSTRING([Column 0],",",1) - 1) derived column 2 SUBSTRING([Column 0],FINDSTRING([Column 0],",",1) + 1,FINDSTRING([Column 0],",",2) - FINDSTRING([Column 0],",",1) - 1) derived column 3 SUBSTRING([Column 0],FINDSTRING([Column 0],",",2) + 1,FINDSTRING([Column 0],",",3) - FINDSTRING([Column 0],",",2) - 1) derived column 4 SUBSTRING([Column 0],FINDSTRING([Column 0],",",3) + 1,LEN([Column 0]) - FINDSTRING([Column 0],",",3) - 1) after derived column transform 1, add destination for table1 and map each derived column to appropriate table column do same for table 2 and table 3, just note that for table 2, derived column transform is like this: Derived Column Name Expression ---------------------------------------------------------------------------------------- derived column 1 SUBSTRING([Column 0],1,FINDSTRING([Column 0],",",1) - 1) derived column 2 SUBSTRING([Column 0],FINDSTRING([Column 0],",",1) + 1,FINDSTRING([Column 0],",",2) - FINDSTRING([Column 0],",",1) - 1) derived column 3 SUBSTRING([Column 0],FINDSTRING([Column 0],",",2) + 1,LEN([Column 0]) - FINDSTRING([Column 0],",",2) - 1) and for table 3, derived column transform is like this: Derived Column Name Expression ---------------------------------------------------------------------------------------- derived column 1 SUBSTRING([Column 0],1,FINDSTRING([Column 0],",",1) - 1) derived column 2 SUBSTRING([Column 0],FINDSTRING([Column 0],",",1) + 1,LEN([Column 0]) - FINDSTRING([Column 0],",",1) - 1) let me know if you have any question regarding implementationhttp://www.rad.pasfu.com My Submitted sessions at sqlbits.com

Hi Maverick008, The best practice is to Use Conditional Split in Data flow and discriminate data for table by implementing filtering criteria and dumped it at desired table. Please refer below link : http://www.bimonkey.com/2009/06/the-conditional-split-transformation Amit Please mark as answer if helpful

@Reza Raad, Amit and Arthur... thank you very much for your help guys... I really appreciate it .... @Reza and Amit .. i followed both of your ways.. the ETL works good... Thank you, Maverick008.