I have a file that I need to transform using SSIS and in looks like this:
Date : 20 SEP 2011
Renewal Business Bordereau for August 2011 - AXC Ins Co - Facultative
PolicyNo, LastName, FirstName, SX, Date Of Birth, Policy Type
========,=========,==========,===,==============,============
0001211 , Oscar , David , M , 29011977 , DEATH
0001213 , Obeten , Samul , F , 29031978 , DISABILITY
0001214 , Owen , David , M , 29031975 , DEATH
Date : 20 SEP 2011
Lapsed Business Bordereau for August 2011 - AXC Ins Co - Facultative
PolicyNo, LastName, FirstName, SX, Date Of Birth, Policy Type
========,=========,==========,===,==============,============
0001424 , Darren , Jacoobs , F , 29031996 , DISABILITY
0001635 , Owen , Bergdal , F , 29031965 , DISABILITY
0001132 , Ahmed , Muhamad , M , 29031967 , DEATH
Date : 20 SEP 2011
New Business Bordereau for August 2011 - AXC Ins Co - Facultative
PolicyNo, LastName, FirstName, SX, Date Of Birth, Policy Type
========,=========,==========,===,==============,============
0001210 , Djibril , Pogba , M , 29031977 , LIFE
0001999 , Thabang , Ngwenya , M , 29031966 , DEATH
I want it to look like the one below in which the movementType and Date columns are created and filled with the data in the section headers like the following
PolicyNo, LastName, FirstName, SX, Date Of Birth , Policy Type , MovementType , Date
0001211 , Oscar , David , M , 29011977 , DEATH , Renewal Business , August 2011
0001213 , Obeten , Samul , F , 29031978 , DISABILITY , Renewal Business , August 2011
0001214 , Owen , David , M , 29031975 , DEATH , Renewal Business , August 2011
0001424 , Darren , Jacoobs , F , 29031996 , DISABILITY , Lapsed Business , August 2011
0001635 , Owen , Bergdal , F , 29031965 , DISABILITY , Lapsed Business , August 2011
0001132 , Ahmed , Muhamad , M , 29031977 , DEATH , Lapsed Business , August 2011
0001210 , Djibril , Pogba , M , 29031977 , LIFE , New Business , August 2011
0001999 , Thabang , Ngwenya , M , 29031966 , DEATH , New Business , August 2011
Note: This is not a combination of more that one file it is a file with different section headers, each header containing the MovementType and the date (movementtype refers to the transaction type and date refers to the transaction date) of which when you look at each header you will see its August 2011. A person can only exist in one section and the policynumber can only be found in one section as only one transaction can take place