CODEWITHSUNDEEP
sql-servermergessisforeign-keysetl
LionOrion
LionOrion

Reputation: 105

How can I get one of my foreign key outputs to repeat in a merge transformation in SSIS?

I tried asking this question before and it seemed to have gotten swept under the rug.

First thing first, here are these two pictures to show the table structure and the current output I get in SSIS.

Table Diagram Table Diagram

Current Output Current Output

So in table three, there is only one entry. This entry (name) applies to the other foreign keys though. What I want the final output to look like is like my current output, but instead of the NULLS, there should just be ones.

I was able to get this far on my own through researching and learning about the merge transformations but I can't seem to find anything on manipulating the data in the way that I want.

I greatly appreciate any tips or advice you can offer.

EDIT: Since the images can't be seen apparently, I will try and describe them.

The table diagram has four tables, the top one in the waterfall has a primary key formed from the three foreign keys for the three different tables.

Trying to accomplish filling out this table in SSIS, my output has each foreign key id from the first two tables, but only one in the third table. The rest from the third foreign key are all NULLS. I believe this is because there is only one entry in that table for now, but this entry applies to all of the foreign key ids and so it should be repeating.

It should look like this:

ID1     ID2     ID3
1       1       1
2       2       1
3       3       1

But instead, I am only getting nulls in the ID3 field after the first record. How do I make the single id repeat in ID3?

EDIT 2: Some additional screenshots of my data flow and merge transformation as requested.

[![SSIS Dataflow][3]][3]

Merge Transformation Setup

Upvotes: 3

Views: 194

Answers (2)

LionOrion
LionOrion

Reputation: 105

After working on this for a few weeks, and with a tips from a colleague, a solution to this question was found. Surprisingly, it was quite simple and I'm slightly shocked that no one on here could provide the answer.

The solution was simply this; Using a data source, write the following SQL code in the data access mode (SQL Command):

SELECT a.T1ID,
b.T2ID,
c.T3ID 
FROM Table1 AS a join 
Table2 AS b
On a.T1ID = b.T2ID,
Table3 AS c
ORDER BY a.[T1ID] ASC

What I Needed

Upvotes: 1

digital.aaron
digital.aaron

Reputation: 5707

If Table3 will always have just a single row, the simplest solution would be to use an Execute SQL task to save the T3id to a variable (Control Flow), then use a Derived Column task (Data Flow) to add the variable as a new column.

If that won't work for you (or your data), you can take a look here to see how to fudge the Merge Join task to do what you want.

Upvotes: 0

Related Questions