Look up failing in Data flow component

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.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Look up failing in Data flow component

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-03-05 : 05:37:45

I have a table who's data I want to increase:


Header table
TranID StoreNo
1      10 
2      10
3      10
4      20
5      20
6      20
7      30

Desired results:


TranID StoreNo
1      10 
2      10
3      10
4      20
5      20
6      20
7      30
8      40 
9      40
10     40
11     50
12     50
13     50
14     60
15     70
16     70
17     70

I have an SSIS package that pumps data from the original back into itself, and updates the storeNo and TransactionID (as tranID is unique). The original transactional data is doubled with each iteration, using the original data set to generate the new set of stores and transactions for those stores. However, I don't want the data to double with each iteration, I ONLY want to repeat the original dataset with each iteration:


StoreNo  NoOfTrans
10       3
20       3
30       1

Iteration 1:
NoOfTrans
6
6
2

Iteration 2,3,4.....:
NoOfTrans
9, 12, 15 (so NOT 12, 24, 48, .....)
9, 12, 15 (so NOT 12)
3, 9, 12, (so NOT 4, 8, 16, ...........)

I have a store mappings table that contains the new store mappings:


Iteration 1:
StoreNo  NewStoreNo
10       40
20       50
30       60

This is updated the second time round to generate the new storeNos:
Iteration 2:
StoreNo  NewStoreNo
10       70
20       80
30       90

I use a lookup component in the data flow to replace the StoreNo with the NewStoreNo. However, on the second iteration, I get the following error:

Error: 0xC0209029 at Data Flow Task, Lookup [181]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Lookup" (181)" failed because error code 0xC020901E occurred, and the error row disposition on "output "Lookup Output" (183)" specifies failure on error.
Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Lookup" (181) failed with error code 0xC0209029. 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.

I presumed this occurred because the data (in red below) that I created in the first iteration does not have a StoreNo match in the reference table:


TranID StoreNo
1      10 
2      10
3      10
4      20
5      20
6      20
7      30
8      40 
9      40
10     40
11     50
12     50
13     50
14     60

I changed the error output to ignore the failure, expecting the values that DO match to still be successful. However, this is not the case. The second iteration produces NO RESULTS???!?

What can I do to change this?

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-03-05 : 06:53:58

Also, I created an error output file to capture the rows that didn't match, and it was empty?

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-03-05 : 07:22:02

Right, I know what is happening.

The second time round (second loop iteration), the store numbers that don't match are being removed.... Fine!

However, the original store number is not being replaced by the new mapping? WHY??

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-03-05 : 07:35:37

YES YES YES YES YES!!

Worked it out. Stupid stupid me out some parameter settings in the wrong place, so when updating the store mappings to the new value (maxStoreID + n), this wasn't happening as MaxStoreID was not being reset with the latest max store. Hence the reason why the value wasn't changing, and thus no rows being inserted (as the index is set to ignore dupes). DOH!!!!!

Hearty head pats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources