Using integration service, how can I copy a record, with all the records that the main record has a foreign key relationship to

Question

I am working on creating the necessary views, triggers and stored procedures so I can make it easier for people to use Integration Service to copy data to and from our database, which is an entity-attribute-value schema, so the foreign key relationships are not always explicitly stated in the schema, but in my view I can hopefully make it more explicit.

So if I have a vehicle entity and I want to copy it, and have all the related parts of the vehicle also be copied, what should I be looking at with the service?

I am not very comfortable with Integration Service so I may ask for some clarification after responses.

Thank you.

Answer 1

SSIS typically loads a single branch of a dataflow into a table. A branch can split to load multiple tables.

I'd say it would be better to load to a staging table which always matches the required expectations for an entity, have the users make their dataflows to populate the staging table and then use a single INSERT/UPDATE in a SQL Command task to update your view (via an INSTEAD OF trigger, right?).

Another good possibility is to create a custom data destination component which enforces all your expectations.