How to Insert or Update Records from Source to Destination using Oracle 8i DB

Hi !

I need help for SSIS Pacakge. using condtional Split How to insert One records with Multiple time depending on Source column value .Is there possible to wrtie the condition in Conditional split.

For Exmaple :

Source Table Name : tbl_source

following Column Name: col_Name1,Col_Name2,Col_Name3, col_Id,Col_Descrip

table contain only one records:GRD1,SRD1,FRD1,100,Product

I want Insert the Destiantion table the Follwing Condition. using Conditional Split.

1)Cond1 (!(ISNULL(GRD1))

2)Cond2 !(ISNULL(SRD1))

3)Cond3 !(ISNULL(FRD1))

I need the Following output

Destination Table Name : tbl _Dest(for One record in source table i need following records in destination)

Coulmn Name , Column Value , ID

Row 1 GRD GRD1 100

Row 2 SRD SRD1 100

Row 3 FRD FRD1 100

How to achieve this result. can u anyone help me.using Conditional split iam getting only first condition Result.

Thanks & regards

M.Jeyakumar

Hi,

You can not do it thru Conditional split. Use script component to do this.

|||Have you try using the Multicast task?|||

Hi , thanks for your repley. can you give samples control flow for this one .

Thanks & regards

Jeyakumar

|||

Maybe something like this...

Multicast Task A -> Conditional Split Task 1 -> Derived Column Task 1 -> Destination 1

Multicast Task A -> Conditional Split Task 2 -> Derived Column Task 2 -> Destination 2

Multicast Task A -> Conditional Split Task 3 -> Derived Column Task 3 -> Destination 3

One Multicast Task that split into 3 transformations.

|||

Hi HienPhilly,

Thank you for your kind of Help. Now its working fine. i designed the above dataflow diagram.now its working fine.i need another doubt in using Lookup in SSIS.

There are two Tables .

1) Product_ tbl

2) Master_Prod_tbl

Using Lookup i want all the Product_tbl column values(i.e Source table) only if Matching the Poduct_tbl_ProductID=Master_Prod_tbl

How to achieve this one ?

the following data flow i am using

1) Product_tbl -> Lookup-->Dstination table

in the Lookup the follwing sql query i wrote:

Select p.GDR,p.CUSIP,p.ISING,p.SEDUL FROM Product_tbl p,Master_Prod_tbl m

WHERE p.CODE=m.CODE

but it didn't work? pls give me your suggestion.

Thanks & Regards

Jeyakumar.M

|||

I don't think you are using it correctly. Under the Reference Table tab, you should have your master_prod_tbl table. Under the Column tab is where you map your lookup column. And if you only want the matched records, you have to Configure Error Output...

|||

Hienphilly wrote:

Maybe something like this...

Multicast Task A -> Conditional Split Task 1 -> Derived Column Task 1 -> Destination 1

Multicast Task A -> Conditional Split Task 2 -> Derived Column Task 2 -> Destination 2

Multicast Task A -> Conditional Split Task 3 -> Derived Column Task 3 -> Destination 3

One Multicast Task that split into 3 transformations.

i think you mean "transformation" instead of "task".|||

Hi Very thankful for your Help. The Problem was i am not configure the Error OutPut. Now its working fine.

I need another Help using Oracle 8i Database

i have two sql statement :

1) Source Sql Statement(select)

2) Update or Insert

How to achive in SSIS Package .is it possible to use Sql Command in SCD Transfermation ? pls give me ur suggestion.

Thanks & Regards,

M.Jeyakumar

|||

Jkumar wrote:

i have two sql statement :

1) Source Sql Statement(select)

2) Update or Insert

How to achive in SSIS Package .is it possible to use Sql Command in SCD Transfermation ? pls give me ur suggestion.

Sorry, I'm not really understanding. Do you want to update some data using data in the SSIS pipeline? If so, use the OLE DB Command component.

-Jamie

|||

Hi ,

Previously i was tried using Sql Server DB .Now i am using Oracle 8i DB.

Using BI How to INSERT Or UPdate Records from Source to Destination.

Condition.:

1) The source records are New Records t hen The Destination should b e INSERTED

2) Already is there then it should be UPDATED

.How to Achieve this using ORAcle 8i. Whatever u already gave the sloution using SCD Transformation .its working in SQL DB.But now ia musing Oracle 8i it throwing Error.

can i use the Sql command for Insert or Update with Where Condition.

Thanks & regards,

M.Jeyakumar

|||

Jkumar wrote:

Hi ,

Previously i was tried using Sql Server DB .Now i am using Oracle 8i DB.

Using BI How to INSERT Or UPdate Records from Source to Destination.

Condition.:

1) The source records are New Records t hen The Destination should b e INSERTED

2) Already is there then it should be UPDATED

.How to Achieve this using ORAcle 8i. Whatever u already gave the sloution using SCD Transformation .its working in SQL DB.But now ia musing Oracle 8i it throwing Error.

can i use the Sql command for Insert or Update with Where Condition.

Thanks & regards,

M.Jeyakumar

I presume by "SQL Command" you mean "OLE DB Command". The answer is yes, you can use it. And it SHOULD work against Oracle.

-Jamie