Merge join: nr of output rows unchanged when amount of input changes

Dear all,

I created a package that seems to work fine with a small amount of data. When I run the package however with more data (as in production) the merge join output is limites to 9963 rows, no matter if I change the number of input rows.

Situation as follows.

The package has 2 OLE DB Sources, in which SQL-statements have been defined in order to retrieve the data.

The flow of source 1 is: retrieving source data -> trimming (non-key) columns -> sorting on the key-columns.

The flow of source 2 is: retrieving source data -> deriving 2 new columns -> aggregating the data to the level of source 1 -> sorting on the key columns.

Then both flows are merged and other steps are performed.

If I test with just a couple of rows it works fine. But when I change the where-clause in the data source retrieval, so that the number of rows is for instance 15000 or 150000 the number of rows after the merge join is 9963.

When I run the package in debug-mode the step is colored green, nevertheless an error is displayed:

Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Merge Join" (4703) failed with error code 0xC0047020. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

To be honest, a few more errormessages appear, but they don't seem related to this issue. The package stops running after some 6000 rows have been written to the destination.

Any help will be greatly appreciated.

Kind regards,

Albert.

If you could post the full error output here, that would probably be helpful. Sometimes it is the "big view" that helps point you at the cause of the problem, especially since many of SSIS's error messages are not particularly transparent. The earlier errors often show what triggered the later errors, even if they do not appear directly related.

|||Can you also include details of the next task in the pipeline, the one that accepts the ~9000 rows. It sounds like it's failing on the first buffer it receives as input.|||

Ok, for the big picture: I feel like a fool.

Solving one of the other errors solved the reported issue as well.

Apologies for bothering you.

Merge Join Output Bug?

I've run into something that looks like a bug to me but I wanted to run it by the board:

Merge join 2 sorted tables.

Table1: ColumnA : Sort Order 1, ColumnB Sort Order 2

Table2 : ColumnA: Sort Order 1, ColumnB Sort Order 2, ColumnC not sorted

Merge Join the two tables on ColumnA and ColumnB...

Choose the following as output columns

A + B + C = works

C = works

A + C = works

B + C = NOT work.. error message: The column with the SortKeyPosition value of 0 is not valid. It should be 2.

Basically if you choose one or more of the sorted columns in the output at least one of them has to be the column with Sort position 1 or you'll get that error.

Is this a bug or intentional? If you do not have sort column 1 in the output that output could no longer be considered sorted... so perhaps the error is related to that (instead of error I'd expect some warning about the sorting). Interesting that it lets you choose C only becuase that also makes the output unsorted.

I see your point Chris.

I think it is intential -

. The reason why B+C not work is because column B has a non-zero sortKeyPosition which indicates the output (to which B belongs) should be sorted (in other words, the output's "isSorted" property is true), but the output can not find a column with SortKeyPosition 1

. As for why C column only works is because the output is then not sorted.

If you think the error message is not very helpful, please log a customer issue through our connect website http://connect.microsoft.com/SQLServer and your request will be addressed soon as appropriate.

Thanks

wenyang

Merge Join Output Bug?

I've run into something that looks like a bug to me but I wanted to run it by the board:

Merge join 2 sorted tables.

Table1: ColumnA : Sort Order 1, ColumnB Sort Order 2

Table2 : ColumnA: Sort Order 1, ColumnB Sort Order 2, ColumnC not sorted

Merge Join the two tables on ColumnA and ColumnB...

Choose the following as output columns

A + B + C = works

C = works

A + C = works

B + C = NOT work.. error message: The column with the SortKeyPosition value of 0 is not valid. It should be 2.

Basically if you choose one or more of the sorted columns in the output at least one of them has to be the column with Sort position 1 or you'll get that error.

Is this a bug or intentional? If you do not have sort column 1 in the output that output could no longer be considered sorted... so perhaps the error is related to that (instead of error I'd expect some warning about the sorting). Interesting that it lets you choose C only becuase that also makes the output unsorted.

I see your point Chris.

I think it is intential -

. The reason why B+C not work is because column B has a non-zero sortKeyPosition which indicates the output (to which B belongs) should be sorted (in other words, the output's "isSorted" property is true), but the output can not find a column with SortKeyPosition 1

. As for why C column only works is because the output is then not sorted.

If you think the error message is not very helpful, please log a customer issue through our connect website http://connect.microsoft.com/SQLServer and your request will be addressed soon as appropriate.

Thanks

wenyang

Merge Join

All,

I need to use Merge Join transformation to join two sources. One is from a PIVOT transformation and one of the output columns is ISSORTED, the other is from an OLE DB Source using a query. The Merge Join transformation requires both input source have to be sorted. I cannot find the ISSORTED property on the OLE DB Source!!

I tried to use Derived/ copy transformations but cannot find the property also. How can set the OLE query sorted in order to use the MergeJoin?

Thanks a lot

Jessie,

You have 2 options.

1) Sort the data in your query in the OLE DB Source using an ORDER BY clause. If you do this you will have to manually set IsSorted=TRUE - you do this in the Advanced Editor.

2) Use a SORT component to sort the data.

Note that setting IsSorted=TRUE does NOT sort the data for you. It only tells SSIS that the data is sorted. If you set IsSorted on a data-path that is not sorted then you will get the wrong results coming out of the MERGE JOIN.

-Jamie

|||Thank you so much Jamie|||

Hi, jamie,

This might be a dummy question.

I got the same problem as Jessie's, but when I went into the Advanced Editor of OLE DB Source, I could not find where to set up the Issorted=True. Could someone help me to find out?

TIA

|||

john zhang wrote:

Hi, jamie,

This might be a dummy question.

I got the same problem as Jessie's, but when I went into the Advanced Editor of OLE DB Source, I could not find where to set up the Issorted=True. Could someone help me to find out?

TIA

It is a property of the output. OLE DB Source only has one output (plus the error output) so its not too difficult to find.

-Jamie

Merge Join

Hello all,

I have a package where I use merge join for two sorted inputs and the output is stored in a raw file.

In another package, the raw file from above package is again merge joined with another sorted input. Now my question is....do we need to sort again the raw file from first package? or is it OK to set the isSorted property to True and define the sort keys?

Thank you.

As long as you know the input is sorted, you can use the issorted property.

If it turns out not to be sorted, you could miss joins.

BobP

|||

Thank you for the reply...

So here in my case I am using a left join (in merge join)....how would I know if the raw file output from first package i sorted or not?

There is no problem to sort it again but really takes a lot of time(as the size is too big).

|||

If you are sorting it and then writing it out, it should stay sorted.

BobP

|||Ok....if it stays sorted then OK.......many Thanks

Merge Join

All,

I need to use Merge Join transformation to join two sources. One is from a PIVOT transformation and one of the output columns is ISSORTED, the other is from an OLE DB Source using a query. The Merge Join transformation requires both input source have to be sorted. I cannot find the ISSORTED property on the OLE DB Source!!

I tried to use Derived/ copy transformations but cannot find the property also. How can set the OLE query sorted in order to use the MergeJoin?

Thanks a lot

Jessie,

You have 2 options.

1) Sort the data in your query in the OLE DB Source using an ORDER BY clause. If you do this you will have to manually set IsSorted=TRUE - you do this in the Advanced Editor.

2) Use a SORT component to sort the data.

Note that setting IsSorted=TRUE does NOT sort the data for you. It only tells SSIS that the data is sorted. If you set IsSorted on a data-path that is not sorted then you will get the wrong results coming out of the MERGE JOIN.

-Jamie

|||Thank you so much Jamie|||

Hi, jamie,

This might be a dummy question.

I got the same problem as Jessie's, but when I went into the Advanced Editor of OLE DB Source, I could not find where to set up the Issorted=True. Could someone help me to find out?

TIA

|||

john zhang wrote:

Hi, jamie,

This might be a dummy question.

I got the same problem as Jessie's, but when I went into the Advanced Editor of OLE DB Source, I could not find where to set up the Issorted=True. Could someone help me to find out?

TIA

It is a property of the output. OLE DB Source only has one output (plus the error output) so its not too difficult to find.

-Jamie

merge input output selection dialog is hanging

Hello

I'm trying to use the Merge component. When i attach a datasource to the the component, the Select Input/Output dialog box should popup.. It does, but VS.NET is hanging and i can only shutdown the procesess...

Any idea how i should solve this? how can i re-register this component?

ps. sql 2005 sp1 is installed.

Thanks
Marco

I am experiencing the same problem.

I have SQL Server 2005 SP1 and Visual Studio 2005 SP1.

I thought I had worked around this in the past by adding the components to the designer in a different order, but that does not seem to make a difference any longer.

|||

If this is something that happened very randomly and could be worked around by just closing/killing the project and open it again, then it's a known issue, the fix will be in SP2. However if you bumped into a constant repro of this, then likely it is something new, in that case please file a bug at our beta place with a brief description of that you got, we will get back to you soon.

Thanks

wenyang

|||

Beta Place closed some time ago.

You can of course raise a PSS support case for full paid support, or just log the bug/request online at MS Connect, effectively the replacement for Beta Place - http://connect.microsoft.com

merge input output selection dialog is hanging

Hello

I'm trying to use the Merge component. When i attach a datasource to the the component, the Select Input/Output dialog box should popup.. It does, but VS.NET is hanging and i can only shutdown the procesess...

Any idea how i should solve this? how can i re-register this component?

ps. sql 2005 sp1 is installed.

Thanks
Marco

I am experiencing the same problem.

I have SQL Server 2005 SP1 and Visual Studio 2005 SP1.

I thought I had worked around this in the past by adding the components to the designer in a different order, but that does not seem to make a difference any longer.

|||

If this is something that happened very randomly and could be worked around by just closing/killing the project and open it again, then it's a known issue, the fix will be in SP2. However if you bumped into a constant repro of this, then likely it is something new, in that case please file a bug at our beta place with a brief description of that you got, we will get back to you soon.

Thanks

wenyang

|||

Beta Place closed some time ago.

You can of course raise a PSS support case for full paid support, or just log the bug/request online at MS Connect, effectively the replacement for Beta Place - http://connect.microsoft.com

Merge altering metadata

I have a column going into a merge DT_WSTR length 50 on both(left/right) sides, yet when viewing the resulting output metadata, the length is 16. (I am viewing the metadata by clicking on the arrows)

This is obviously wrong.

NB originally the length was not set to this. As an attempt to try and fix the problem, I have added a placeholder column before all of my conditional splits of str50 type.

Why is it doing this?

This is causing my 0xC02020C1 error, I am convinced of it.

One of the errors has now gone away but the other two merges still have the error.

SSIS seems to assume that you will make no mistakes and will never want to go back and change anything, woe betide you if you have to go back and alter it later.

I have also noticed that certain (other) errors go away merely by opening and closing the tasks. Shocking.In the merge transformation, delete the row corresponding to the bad metadata by right clicking on the column name and selecting delete.

Then close the merge transformation editor. Edit it again and then at the bottom select the column again for both inputs -- matching them up. This should clear the stale metadata.sql

Merge a PDF File and SQL Reporting Services Output

Does anyone know how i can go about merging preexisting pdf files and SQL server reporting services output. Can this be done in reporting services? For example, I have 5 pages from a pdf files which is created from another 3rd party software provider. I then i have output from sql reporting services. How can i merge these two outputs and deliver it over .Net/ ASP framework?

thanks

You could print the pdf to an image writer and use the images as background images in your Reporting Services report.

Or if the Reporting Services output is mainly data you could look at using the output data as input into you thrid party application (or vise versa).

hth

-- Leah

|||

I've tried what Leah suggested and the creation of the gif file was identical to the pdf (same width and height). However, when I used it as a background in the VS2005 report designer, the image became larger. How do you maintain the same dimensions of the original image when it is used as a background image?

Thanks

|||I came across a post from a different forum that was the closest to solving this issue. The solution was to scan a document at 96dpi prior to using it as the background. It wasn't perfect but it got me into the ball park where I could adjust positioning of the controls on a case by case basis.

Merge a PDF File and SQL Reporting Services Output

Does anyone know how i can go about merging preexisting pdf files and SQL server reporting services output. Can this be done in reporting services? For example, I have 5 pages from a pdf files which is created from another 3rd party software provider. I then i have output from sql reporting services. How can i merge these two outputs and deliver it over .Net/ ASP framework?

thanks

You could print the pdf to an image writer and use the images as background images in your Reporting Services report.

Or if the Reporting Services output is mainly data you could look at using the output data as input into you thrid party application (or vise versa).

hth

-- Leah

|||

I've tried what Leah suggested and the creation of the gif file was identical to the pdf (same width and height). However, when I used it as a background in the VS2005 report designer, the image became larger. How do you maintain the same dimensions of the original image when it is used as a background image?

Thanks

|||I came across a post from a different forum that was the closest to solving this issue. The solution was to scan a document at 96dpi prior to using it as the background. It wasn't perfect but it got me into the ball park where I could adjust positioning of the controls on a case by case basis.