It is a very common scenario to be able to load two tables in the same data-flow that have a foreign key relationship between them. The question of how to do this gets raised very often on the SSIS forum so I figured I'd try and address it here.
Basically, its not possible to do this without using a workaround. There are two possible workarounds that I can think of:
1) Load the table containing the primary/unique key constraint within the data flow and drop the data destined for the table with the foreign key constraint to a raw file using the raw file destination adater. You can then load the second table in a subsequent data flow by pulling the data straight out of the raw file using a raw file source adapter. I have discussed this technique in a bit more detail here: http://blogs.conchango.com/jamiethomson/archive/2006/05/22/3974.aspx
2) Disable the foreign key constraint prior to loading the two tables and then re-enable it again afterwards. Disabling can be done using the following DDL:
ALTER TABLE <table_name> NOCHECK CONSTRAINT <constraint_name>
You can re-enable again after executing the data-flow with the following DLL:
ALTER TABLE <table_name> WITH CHECK CHECK CONSTRAINT <constraint_name>
Don't forget the "WITH CHECK" part when you are re-enabling the constraint otherwise the data that you have just loaded will not not be checked for referential integrity.
I always used to use method 1 but of late I have started to use method 2, simply because it is quicker to build and you don't have to drop data out into raw files.
Which method do you use?