July 22, 2013 at 12:14 am
Dear All,
Have a nice day for all.
We have a scenario for out project.we have extract data from oracle sourece to Sql database.we have a cloumn in Location in oracle table,Location is three type either 'SOAP','POWDER','COMMON',if SOAP means i need to insert the 3 in sql table and POWDER means i need to insert the 4 in sql table.If COMMON means ,i need to insert 2 records in SQL for SOAP and POWDER.
SAMPLE OUTPUT for ur unserstanding:
oracle:
id name location
1 a SOAP
2 b POWDER
3 C COMMON
Target Table
id name locationid,address
1 a 3 chennai
2 b 4 bangalore
3 c 3 mumbai
4 c 4 Mumbai
how to handle this scenario in ssis.
please help me on this.
July 22, 2013 at 12:19 am
Can you write a query which executes against the Oracle database and brings back the data in the format you want?
If you can, job done - use that as your data source.
If not, why not?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 22, 2013 at 12:32 am
hi parkin,
please can u explain just brifly....
July 22, 2013 at 12:36 am
Create a query which runs against your Oracle data.
The query should return the data in the format you want to insert to SQL Server.
Use the query within a data flow as an OLEDB Source.
Add your SQL Server table as the OLEDB destination, join it to the source and map the columns appropriately.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 22, 2013 at 1:02 am
The easiest way woul be to use a script component as a transformation.
Use a switch statement on the location column to determine the locationID.
If it is "common", you need to add the extra row, making the script component asynchronous.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 22, 2013 at 1:04 am
I kept thinking about this scenario, and you can also implement it without a script, using only SSIS components.
Put a conditional split that checks the location. You have 3 outputs: soap, powder and common. After the common output you put a multicast to duplicate the stream.
Each path now has it's own derived column that calculates the locationID.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 22, 2013 at 4:11 am
hi koen,
thanks for ur reply,almost i close to target as ur sugestion.
upto multicast i have completed,how to map the target column location 3 instead of SOAP
and 4 instead of POWDER and COMMON means i need to insert the two rows one for SOAP and another for POWDER.
final condition if new rows are coming to need insert the target,and if existing means need to update the target.
i am stuggling in MAP colmun Location Source to target Location ID column:
example
Source:
location
SOAP
POWDER
COMMON
Target
3
4
3-soap
4-powder
please sugges me step by step action.
July 22, 2013 at 4:46 am
After the conditional split and the multicast you have 4 different paths.
Add a derived column to each path.
In the derived column, you simply add a new column and put the value there. Ex. 3 for the soap path.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 22, 2013 at 6:41 am
hi,
i added derived column,how to map the new column to target column.i am confused.
i am not solution.
please help me this!.
in target location id one column only how we map the SOAP ,POWDER,& COMMON id like(3,4,and Common means need to insert 2 records )
July 22, 2013 at 7:15 am
Put all the flows back together with a UNION component and write it to the destination.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply