I am new to SSIS and I have been playing with it for the last couple of days. I would like to know if anybody can give me some feedback or point me in the right direction ( best by looking at an example ) to acomplish the following :

I have a table called Policy ( free foxpro table ). This table contain policy insurance information and also customer information. I would like to upload and normalize this table to an existing "PolicyAttributes" SQL Server 2005 table.

Also I would like to create a new record in the Customer table if there exist a customer in the policy Table, thus separating the customer information from Policy table ( normalizing the table ).

The policy table has a field called CustomerName_1 if it is empty then this policy does not have any cutomer, but if it has a name then I would like to insert a new record into the SQL Server Customer table and then get the unique number from the newly created record in Customer table and update it in the SQL Server Policy table ( let say CustomerID ). This way I can keep the relationship PolicyAttibutes --->Customer on the CustomerID field.


So basically here are the logical steps:

1.- Read Policy records from foxpro table
2.- insert record to SQL Server PolicyAttributes table
3.- for each customer name in the foxpro Policy table insert a new record in the SQL Server Customer table.
4.- Get the unique number from the new Customer record ( Primary Key of Customer record) and update this number in the PolicyAttibutes field called CustomerID ( see above step 2 ).
This way I can keep my PolicyAttributes---->Customer relationship.


Also the foxpro Policy table can contain more than one customer. It can have up to 4 customers.
So if there are 4 customers then I will have to insert 4 records in the customer table.

CustomerName_1
CustomerName_2
CustomerName_3
CustomerName_4


Any help is greatly appreciated!.
Thanks,
Sergio