Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
colherdepau
Starting Member
2 Posts |
 Posted - 2009-02-09 : 11:55:28
|
| Hi,i'm new to ssis and i am in a bit of trouble in a data flow task, any help appreciated. Thanks in advance.this is what is happening:i have a lookup transform with this input (ID, Citykey)(this is meant to get the surrogate key for the specified citikey).in case it doesn't match(failures) i want the row to set ID = -1 (to do this i added a "derived Column" transform to add a column with name ID and value -1Now i want to merge the two datasets(one coming from the failures with id=-1 an the other comes from the successful matches in the lookup, both have same column structure).to do this i've sorted both datasets, and then the used a union all, yet this doesn't work.any thoughs or ideias on how to do this?i also tried with merge and merge join, unsuccessfully. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-09 : 12:25:04
|
| why it should ideally work. whats the error you got? also have you checked and confirmed that mappings in union all are correct? |
 |
|
|
colherdepau
Starting Member
2 Posts |
Posted - 2009-02-09 : 13:44:23
|
| There is no error, the problem is that the rows don't pass through the "union all" transform, if they only come from one of the sides/sourcesAn even if they come from both sources of the union all transform, only a few pass!As for the mappings in "union all" transform:Output col | Input 1 | Input 2ID ID IDCityKey | CityKey | CitykeyI guess the records only pass when there's a match..which is weird..isn't the union all transform supossed to just add rows from both of the sources? |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-02-09 : 15:45:12
|
| Union All will combine both the outputs if they are of same structure. It will even include duplicates. So whatever you are explaining should work.Can you give the exact data that you are doing a union All on? Also can you tell which data was lost in the Union All? That will help. |
|
|
|
|
|
|
Mergging two datasets with same table structure