ssis - combining two sources with no relation

Question

ssis - combining two sources with no relation

vineet_

SSC-Addicted

Points: 475
More actions
July 14, 2010 at 9:14 pm

#144004

Hi all,
I there any way to join two sources with no relation...
I have a Stored procedure to generate a unique ID(custom ids) based on number of rows I fetch from the source, but the problem is that the ID's generated are inserted into a separate table by the stored procedure itself, now I have to find a method to use those ID's in my data flow and join those with my source returned rows, and insert both IDs and other information to my destination....
but as I don't have anything to lookup on I cant join it with data flow rows, also I can join any ID with any row...
This is not happening
Any help on this...
,
------------------
Why not ?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246965 More actions · Answer 1

Please provide sample input data, along with desired output data.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

vineet_ SSC-Addicted Points: 475 More actions · Answer 2

Actually, I dont have any input in a way to be presented, Its just I want an approach to do this problem.

I can put it in steps like this:

> OLE DB source will return 10 rows

> Existing Stored procedure will generate 10 ID's for those 10 rows from the source and put them in a separate table (say tableB) to be picked up from.

> Now using the same OLEDB src I have to lookup that table (tableB) and associate in pid with each row.

> but as the PIDs are generated by a stored procedure and are placed in a separate table, I should have some process to join with that table, but there is no relation so how can I join and get PIDs from that table to this table.

> or may be ask my stored procedue to instead of inserting it in separate table , use them in the flow return them.

any help?

Thanks

------------------
Why not ?

Phil Parkin SSC Guru Points: 246965 More actions · Answer 3

OK, I understand what you mean.

I'd be tempted to use ranking functions to assign a row number (order by ID, or whatever you want) to each row in both tables, then inner join them on row number - you can do the whole thing in T-SQL.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

sinureddi SSC Journeyman Points: 97 More actions · Answer 4

Hi,

If your SP does not do any DML operations, write a function instead and use it in the inline query.

Srinivas

anisha.nair Newbie Points: 4 More actions · Answer 5

[font="Comic Sans MS"]Instead of using a stored proc to create IDs, in the OLD DB source task, where you are selecting your records, you can use the "newid()" function as a column. This way you can do away with the other Source and merging the two inputs.[/font]

Langston Montgomery SSC Eights! Points: 934 More actions · Answer 6

Unless I am missing something, I think what you said below would be your best bet... just return the IDs in the flow... not sure why they need to be stored in a different table and then rejoined later in the flow.

vineet_ (7/15/2010)
> or may be ask my stored procedue to instead of inserting it in separate table , use them in the flow return them.