Good day, i need help. I have two Sources, Source1 has 600 Records & Source2 has 200 Records. Merge Join on Source2 with Left Outer Join to Source1 In my Sources queries, i'm order by on 3 cols & issorted & the data types matches with the joining on these 3 cols. But i'm returning 450 records, i should return at least 600 records with Nulls where there is no match. I aslo checked my Source if there are any nulls on the joining 3 cols. Please Assist, what am i doing wrong

your both sources are table?? If yes, better use a SQL query with join condition and extract the data. Let us TRY this | My Blog :: http://quest4gen.blogspot.com/

Unfortunately, 2 different databases :(

Hi, I think the problem is with source only,set the sort key position property for the sorted 3 columns in both the data sources, and check once,

Hi, I do have the 3 cols sortkeypositions set for both Data Sources together with the Order by on the Source Query. Still no luck :(

Good day, i need help. Merge Join on Source2 with Left Outer Join to Source1 In my Sources queries, i'm order by on 3 cols & issorted & the data types matches with the joining on these 3 cols. HI Why dont you Just simply add 2 different sort transformation [sort on each columns which u have in question ] after each oledB source then direct the sort transform output to merge join . See what happenes ... lets know if u face similar Logical err .. Hope that helps ... Kunal

Hi, i have tried that as well - no success still returns 450 records, but i noticed when i join on only the 2 cols i get more records like 730 but the moment i add the 3rd col join, i go back to 45o records returned. There is no nulls & the cols are trimmed & sorted ?

Hi, i have tried that as well - no success still returns 450 records, but i noticed when i join on only the 2 cols i get more records like 730 but the moment i add the 3rd col join, i go back to 45o records returned. There is no nulls & the cols are trimmed & sorted ? Are you sure you are doing (Source1 -> Sort1) LEFT OUTER JOIN transformation (Source2->Sort2) ? Sort1 And Sort 2 both transformations the sorting of all the coulmns order is same ? Hope that helps ... Kunal

Yes for both, double checked everything

I'm in a Puzzle, i'm not to sure if the Merge Join is working for me. The Main Source is Source1 (600 UnGrouped) records must return All records in Source1 plus where there is no match in Source2 (200 Grouped) records. If Source2 has 50 records thats not in Source1 then my output must be 650 Records. Please help.

Any Ideas on the Below Please: The Main Source is Source1 (600 UnGrouped) records must return All records in Source1 plus where there is no match in Source2 (200 Grouped) records. If Source2 has 50 records thats not in Source1 then my output must be 650 Records.

Any Ideas on the Below Please: A : The Main Source is Source1 (600 UnGrouped) records must return All records in Source1 plus where there is no match in Source2 (200 Grouped) records. B : If Source2 has 50 records thats not in Source1 then my output must be 650 Records. Dude i guess there is some gap .... what do you mean by Grouped and ungrouped records ????... going by your new explanation i guess what u need is probably the UNION TSQL like of 2 result sets. can you share your business requirement ??? like what exactly your source to destination ETL problem statement would be ?? Hope that helps ... Kunal

Good day, i need help. I have two Sources, Source1 has 600 Records & Source2 has 200 Records. Merge Join on Source2 with Left Outer Join to Source1 In my Sources queries, i'm order by on 3 cols & issorted & the data types matches with the joining on these 3 cols. But i'm returning 450 records, i should return at least 600 records with Nulls where there is no match. I aslo checked my Source if there are any nulls on the joining 3 cols. Please Assist, what am i doing wrong Okay, if you are expecting at least 600 records, then you mean to say that Source1 is LOJ'd to Source2. If you have everything setup correctly, then confirm that the CasE sensitivity between the comparison columns is the same. A value of "Phil" in Source1 will not match a value of "PHIL" in Source2. PhilPhil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

Hi, i have created a work around not really what i like. The reason for comparing the two sources is that Source1 is the new Database & Source2 is the old Database which contains our Sales Files. The monthend procedure updates the Old DB correctly but not the New one (Working in progress to fix) So i'm developing on new DB for this package but cant balance as records are missings, so i need have all in New DB Plus whats missing records that exists in old DB. The Old DB has already done all the lookups & grouping etc, where New DB I still have to fo that in this package there fore the records are more. I ended up createing a new table to store both datasets, with different Types being 'Source1' & 'Source2' Then in an additional task, Union All the two source into the new table. I could now have a source where Type = 'Source1' then a lookup to Type = 'Source2' and OnFailure pass records with 'Source1' to actual table. Like i said not what i like as I have unnecessary table & additional tasks - but i have been struggling for a while & need to complete project. Perhaps if no other ideas, Is there a way of Creating & Droping a temp table in a Execute SQL Task (Please provide example) Thank You All, Please Assist with any ideas?

Hi, i have created a work around not really what i like. The reason for comparing the two sources is that Source1 is the new Database & Source2 is the old Database which contains our Sales Files. The monthend procedure updates the Old DB correctly but not the New one (Working in progress to fix) So i'm developing on new DB for this package but cant balance as records are missings, so i need have all in New DB Plus whats missing records that exists in old DB. The Old DB has already done all the lookups & grouping etc, where New DB I still have to fo that in this package there fore the records are more. I ended up createing a new table to store both datasets, with different Types being 'Source1' & 'Source2' Then in an additional task, Union All the two source into the new table. I could now have a source where Type = 'Source1' then a lookup to Type = 'Source2' and OnFailure pass records with 'Source1' to actual table. Like i said not what i like as I have unnecessary table & additional tasks - but i have been struggling for a while & need to complete project. Perhaps if no other ideas, Is there a way of Creating & Droping a temp table in a Execute SQL Task (Please provide example) Thank You All, Please Assist with any ideas? Okies as i said what you actually required Tsql UNION sort of transformation : NEW {Source 1 } AND OLD {Source2 } records in set theory what we say as : A U B . You Need Not load into separate table ... just do 1. Load SOURCE1 data into your destination table straight away 2. Now take Sourec2 as source and do the look up like you are doing already, instead look up on target table itself you can omit have another table with sourceType column that you are having ... there can be several ways to do ... as well . I dont think there is any staraight away transformation that would do UNION operation for you Hope that helps ... Kunal

Thank You, this is basically what i did. But i now have additional temp table & do not want to cluster the Database with Temp tables. Is there a way of Creating & Droping the table using a Execute SQL Task. I have read somewhere that package will fail if the table still to be created at runtime is a destination table in another task. I will only be using the table for this container, please explain how on doing this if possible.

Thank You, this is basically what i did. But i now have additional temp table & do not want to cluster the Database with Temp tables. Is there a way of Creating & Droping the table using a Execute SQL Task. I have read somewhere that package will fail if the table still to be created at runtime is a destination table in another task.-- check below I will only be using the table for this container, please explain how on doing this if possible. Okies if u check my above solution u neednot have this temp table at all... but if you insist : A. Create this temp table in DB and develop your package -- i believe u have done that already . just add an execute sql task [after your DATA FLOW TASK ] where write your query to drop your temp table. B . now create another package : here create an execute SQL task where in write your create table script . save this package Now whenever u run this A package ensure that you run B 1st in order to ensure that your temp table is created and in place before u run your actual A package for ETL. i hope that answeres your question in BOLD above .. Hope that helps ... Kunal

Thank You Very Much. The Reason why i could not use the same table as destination being that the the final destination table has different columns & structure. The Source1 & Source2 were the staging for this final destination table where lookup are done from the source then output to detination table. Thank You All for Assisting