Hi Experts ,
I am working on SSIS 2012.
I have to Load Data of Flat File in SQL Table Based on Column Condition.

SAMPLE Flat File is in below format:

NAMEA  ROLLNO
A1        1

or

NAMEB RoLLNO
B1        2

File has Two Column,
1st -NAMEA/NAMEB  
2nd- ROLLNO .
CONDITION: When I Load Data I have to Check 4th Char of 1st Column i.e if 4th Char is "A" example NAMEA then I have to Load that Flat File Data in Table A,
If 4th Char is "B" example NAMEB then I have to Laod the data of that Flat file in TableB

 

Programatically you can read the input source file columns using SCRIPT task and import it.

http://sql-bi-dev.blogspot.jp/2010/09/script-task-to-read-file.html

http://www.selectsifiso.net/?p=3

Hi RSingh,
Thanks for the Reply

Is there any Solution to acieve above scenario with Out Scripting ,As I am not familier with Code and Scripting :(

If you can bring the columns in the source file as,

NAMEA     NAMEB     ROLLNO

then, we can use multicast transformation to mapp input columns to destination columns. Either  the first or the second column can be blank at a time, however the format remains fixed. Refer,

http://www.msbiguide.com/2012/11/multicast-transformation-in-ssis-with-exampl

Hi RSingh,

Thanks for the Reply .
File will have any One i.e either NAMEA or NAMEB as Column Name.

Hi Rihan,

Do you mean that the same file can contain data for both the tables? Or, the file contains the data for either TableA or TableB (but not for both) at a time?

• I would read the column names(NAMEA  ROLLNO) as Row 1 to determine which target table the data should go to.
• To be able to do that, all the columns have to be red as string & a Data conversion must be used to cast ROLLNO to INT

Hi Nitesh,

Yes ,Same File Could contain Data of BOth the SQL Table(Same Structure) .
Only On the base of 1st Column Name we need to Identify that In which Table We should Load the Data of  that Single Flat File .
Any help

Hi RajKumar,
Thanks for the Reply.

Can U please elaborate(Explain in more Detail as how it is to be done ) the Logic ?