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
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...|||
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.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
I hope this helps.
|||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
New feature requests may be submitted here: SQL Server Feedback|||Can you explain what you mean to accomplish by "merge them upon a range condition"?|||I guess merge join are not flexible enough for now, maybe MS will had non equijoin fucntionnality in future version.
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
|||
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
