May 12, 2011 at 3:09 pm
Hi,
I have a source table with customer and address information. I have to check customer table (related to), address table (related to), city table and also customer_address table. I have to go in sequence to check for new or existing records according to which I have to add or update data in these related tables. Started the package where I am able to insert new data to customer but, after that I am confused to design the package as the address and other tables are need to be checked for existence. Please give me some idea about the flow (and types of data flows) I have to use to update all the tables. Help is very appreciated.
May 13, 2011 at 4:27 am
Need to detail as your requirement is not pretty clear ..
__________________________________________
---------------------------------------------------
Save our mother Earth. Go Green !!!
May 13, 2011 at 4:35 am
Use lookup task
May 13, 2011 at 7:51 am
Thank you for the response. Lookup is required for the package but I want to know the flow of the data. In short I have the following tables which need to be loaded:
Customer(custId,...), Address(addrId, cityId,...), City(cityId,....), CustAddress(custaddrId, custId, addrId,...). Source table will be having the data for above tables with no ids. The flow, I guess, should check for existence of customer if yes then update, if no add. Before that check for address for existence, before updations check for city.....
Does it need only one control flow or multiple, some brief idea on the structure of the package. Hope my explanation is clear. Thank you for your time.
May 15, 2011 at 11:10 pm
radb4u (5/13/2011)
Thank you for the response. Lookup is required for the package but I want to know the flow of the data. In short I have the following tables which need to be loaded:Customer(custId,...), Address(addrId, cityId,...), City(cityId,....), CustAddress(custaddrId, custId, addrId,...). Source table will be having the data for above tables with no ids. The flow, I guess, should check for existence of customer if yes then update, if no add. Before that check for address for existence, before updations check for city.....
Does it need only one control flow or multiple, some brief idea on the structure of the package. Hope my explanation is clear. Thank you for your time.
One option I think of is to use MERGE statement (inside Execute SQL Task) for loading data into your table. MERGE will do the Insert (If not exists), Update (If exists) operation on behalf of you. But again this option is useful if you are not applying any transformation on the rows before loading it into your destination table.
Other way, as suggested by srikant, you can use LookUp transformation to look up the CustID, if existing, in your destination table. Add rows in to the 'Look up no matched output'.
__________________________________________
---------------------------------------------------
Save our mother Earth. Go Green !!!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply