Combine raw files using range

ccote

HI, I have a dataflow that has two raw files as source and I would like to merge them upon a range condition:

RawFile1.Date <= RawFile2.Date

Usually, using tables, I would have used a lookup with partial cache to achieve it. Now, since we cannot use lookup transform with raw files, I was wondering how I could achieve this using raw files as source. Is it possible to merge raw files using merge or merge join

Thank you,

Ccote

Phil Brammer


Sure, you can merge two raw files. You can merge any two data flow streams. The data just has to be sorted first.

You could load a staging table for each raw file, though, and write a SQL join against them to get your results as well...

Duane Douglas

ccote wrote:

HI, I have a dataflow that has two raw files as source and I would like to merge them upon a range condition: RawFile1.Date <= RawFile2.Date Usually, using tables, I would have used a lookup with partial cache to achieve it. Now, since we cannot use lookup transform with raw files, I was wondering how I could achieve this using raw files as source. Is it possible to merge raw files using merge or merge join Thank you, Ccote

This appears to be a problem that requires SQL, meaning that both data sets need to be stored in tables. As suggested previously, the data sets can then be joined to yield the desired result set.

I hope this helps.

ccote

Thank you both for your inputs. I will have to revise the design of this package. I wanted to get rid of the table solution since SSIS is installed on an application server (different than SQL server). By using raw files, I would be able to do all the work locally and at the end send the result to SQL server target table in one pass. I guess merge join are not flexible enough for now, maybe MS will had non equijoin fucntionnality in future version.

Thank you,
Ccote

Duane Douglas

ccote wrote:

I guess merge join are not flexible enough for now, maybe MS will had non equijoin fucntionnality in future version.

New feature requests may be submitted here: SQL Server Feedback

Cim Ryan

Can you explain what you mean to accomplish by "merge them upon a range condition"

Jamie Thomson

ccote wrote:

Thank you both for your inputs. I will have to revise the design of this package. I wanted to get rid of the table solution since SSIS is installed on an application server (different than SQL server). By using raw files, I would be able to do all the work locally and at the end send the result to SQL server target table in one pass. I guess merge join are not flexible enough for now, maybe MS will had non equijoin fucntionnality in future version. Thank you, Ccote

Yes. The lack of non-equi-join functionality really annoys me. I once enquired why it was not there and was told "nobody has asked for it". Well...it seems people are now asking for it.

A connect submission for this already exists: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx FeedbackID=126376 so please please please click-through, vote and ADD A COMMENT (just voting is next to useless - they need to see real reasons why this needs to be implemented). You can link back to this thread as well.

-Jamie

ccote

HI, I would like to be able to do basically the same thing as I can do when I join tables: Be able to join on col1 <= col2. One way to achieve this would be being able to to <= (or <>, <, >, etc.) when I use merge join transform. Or,by being able to lookup against raw files, currently, we can only use lookups transforms against reelational tables. If we could be able to do lookups against raw files, it would be perferct since lookups can have parameters and we can specify the query by using ranges.

Ccote