StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
2240688
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
0
CommunityOwnedDate
CreationDate
2010-02-10T22:05:02.113
FavoriteCount
0
LastActivityDate
2010-02-10T22:05:02.113
LastEditDate
LastEditorUserId
0
OwnerUserId
139938
ParentId
2211840
PostTypeId
2
Score
1
ViewCount
0
LastEditorDisplayName
text
Body
Here's how - it's slightly difficult to explain in text only, but I'll give it a shot: Define the employee, office and job title tables in your database with an identity column, so that IDs will be generated automatically. Define the many-to-many tables without (identity is not needed or useful) In your SSIS data flow, you have to do this in a couple of passes, in order to establish the IDs in the database first, then come back and insert the many-to-many rows. Make one data flow: <ol> <li>Put a data source to read in the file</li> <li>Split it into three copies with a Multi-Cast. One will be for employees, one offices, one titles.</li> <li>For each, put a Sort (this is generally a no-no, but since the source is text and not a database, we have to go there). Set the sort to pass through only one of the three fields, and check the option in the sort to remove duplicates. This makes a unique list (like select distinct) for each base table.</li> <li>Put a destination for each of the three, connected to each table.</li> </ol> After the first data flow, add a second data flow. This one will populate the many-to-many relation rows <ol> <li>Read the file with a data source</li> <li>Add a lookup that finds for Employee Name in the database and returns the Employee ID. That gets you the Employee ID that was generated above. (this is usually called lookup by business or natural key for surrogate key)</li> <li>Add a lookup that finds for Title in the database and returns the Title ID</li> <li>Add a lookup that finds for Office in the database and returns the Office ID</li> <li>Again, multi-cast the results into two copies, one for employee-office and one for employee-title</li> <li>Depending on the logic you need, perhaps use Sort, again, to deduplicate these (depends on the detail of how you are normalizing from the input)</li> <li>Put the results into the many-to-many tables with two destinations.</li> </ol>
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POLoading Multiple Tables using SSIS keeping foreign key relationships
 singulars
 PostTypePostTypeId
 PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. This table or related slice is empty.
UserOwnerUserId
1. USonupdatecascade
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. This table or related slice is empty.
CommentsPostId
1. This table or related slice is empty.

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.