Is there an example to accomplish the following?

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

I yhink the simplest way you can resolve your problem is:
1. With Import/Export Wizard copy foxpro records in and SQL table named SQL_Policy. Save the procedure as a package in a job to run time to time.
2.Create an "on insert" trigger on SQL_Policy and do what you want to do. Here you can use try/catch for errors, cursors etc. all in T-SQL

This procedure thinking you are new in SSIS.|||

Sergio:

I am not sure that you are going to find an example specific to your situation. If you are new to SSIS but have done programming before I would start of by first creating a logical flow of your data in Visio. You would then know what kind of transforms you would need. You can then look at the SSIS pallette and choose the ones appropriate for your situation. If you need help in using a particular transform use Books Online to get more information.

I apologize if I am not giving you the specific solution you are looking for but, as I said, every flow is going to be different.