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 databases

I have 2 database, one has data and one is being created on my handheld. Is there a way to merge the database together so I get the data in the tables I need in the database being create or can I add tables to the existing database so I can use that for my testing?

here is the issue;

database 1 has tables with data - but does not have all the tables I need. - pre-existing

database 2 is being created when I start my app, and creates the tables I need but no data and not all the tables.

Is there a way to merge these to, so I don't have to create a huge insert statements for each tables I need data for?

or can I do a select from database 1 and insert the records into database 2?

example: insert into tbl1 (name) select database 2.table(col1) from tbl1, is this possible?

No. "Huge" insert/select/create statements are needed to do that.

|||how would I do that without creating a new database? How would I create the tables for the existing database?|||To create the tables in the existing database, use the "CREATE TABLE" statement.|||

Good day all,

I too am having the same issue. I have 2 databases with diverging information. The tables should all be identical. I've got different developers adding data to 2 different databases. Rather than re-entering data to one or the other database, I'd like to be able to merge the databases. I am by no means a SQL developer, so i'm not terribly familiar with the T-Sql commandset. If there is any more information on this subject, I will be watching this thread.

Thank you in advance for any assistance!

Merge always deletes inserts on subscriber

Hey.
Table1 replicates to table3
Table2, just another table with the same schema as Table1 and 3.
In my setup, I created a copy table, copied 66 rows from table1 on publisher
to table2 on subscriber and deleted all 66 rows. The changes were replicated
to the subscriber to Table2(66 deletes made). Now i insert all 66 rows from
Table2 to table3 on the subscriber side. The change should have gone to
publisher. Instead, the inserts get deleted on the subscriber... Why would
SQL do this?
The inserts done later should be taken as new changes on subscriber and
should be replicated to publisher, isn't it?
Forgot to add, it took it as a conflict between pub and sub and for me, by
default, the pub wins... The problem with this is though, it shouldn't be a
conflict. I've ADDED these rows right now and ONLY to the subscriber...
Any clues.