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

How to Insert One Row with Multiple time depding on Source Column value in SSIS Pacakge

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

How to import XML

I grabbed the following .xml and .xsd files from an online tutorial, and tried to import them into SQL Server by using XML Source. I want to be able to import ALL the data; however, the simpleType elements under the root <shiporder> are all missing from the XML source, e.g <orderpersion>. The root element, including any attributes from the root element are also missing...e.g <shiporder orderid="889923"

How can I import these missing fields? The .xml and .xsd files are below.

Any help will be greatly appreciated. Thanks.

.XML and .XSD files below. please note that the website replaces ": s" with

shiporder.xml
-
<?xml version="1.0" encoding="ISO-8859-1"?>

<shiporder orderid="889923"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="shiporder.xsd">
<orderperson>John Smith</orderperson>
<shipto>
<name>Ola Nordmann</name>
<address>Langgt 23</address>
<city>4000 Stavanger</city>
<country>Norway</country>
</shipto>
<item>
<title>Empire Burlesque</title>
<note>Special Edition</note>
<quantity>1</quantity>
<price>10.90</price>
</item>
<item>
<title>Hide your heart</title>
<quantity>1</quantity>
<price>9.90</price>
</item>
</shiporder>
-

shiporder.xsd...
-
<?xml version="1.0" encoding="ISO-8859-1" ?>
<xschema xmlns:xs="http://www.w3.org/2001/XMLSchema">

<xsimpleType name="stringtype">
<xs:restriction base="xstring"/>
</xsimpleType>

<xsimpleType name="inttype">
<xs:restriction base="xsositiveInteger"/>
</xsimpleType>

<xsimpleType name="dectype">
<xs:restriction base="xsecimal"/>
</xsimpleType>

<xsimpleType name="orderidtype">
<xs:restriction base="xstring">
<xsattern value="[0-9]{6}"/>
</xs:restriction>
</xsimpleType>

<xs:complexType name="shiptotype">
<xsequence>
<xs:element name="name" type="stringtype"/>
<xs:element name="address" type="stringtype"/>
<xs:element name="city" type="stringtype"/>
<xs:element name="country" type="stringtype"/>
</xsequence>
</xs:complexType>

<xs:complexType name="itemtype">
<xsequence>
<xs:element name="title" type="stringtype"/>
<xs:element name="note" type="stringtype" minOccurs="0"/>
<xs:element name="quantity" type="inttype"/>
<xs:element name="price" type="dectype"/>
</xsequence>
</xs:complexType>

<xs:complexType name="shipordertype">
<xsequence>
<xs:element name="orderperson" type="stringtype"/>
<xs:element name="shipto" type="shiptotype"/>
<xs:element name="item" maxOccurs="unbounded" type="itemtype"/>
</xsequence>
<xs:attribute name="orderid" type="orderidtype" use="required"/>
</xs:complexType>

<xs:element name="shiporder" type="shipordertype"/>

</xschema>

The XML Source expects a dummy root for some reason. Adding one to the XML and XSD should get you what you need.

Code Snippet

<root>
<shiporder orderid="889923"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="shiporder.xsd">
<orderperson>John Smith</orderperson>
<shipto>
<name>Ola Nordmann</name>
<address>Langgt 23</address>
<city>4000 Stavanger</city>
<country>Norway</country>
</shipto>
<item>
<title>Empire Burlesque</title>
<note>Special Edition</note>
<quantity>1</quantity>
<price>10.90</price>
</item>
<item>
<title>Hide your heart</title>
<quantity>1</quantity>
<price>9.90</price>
</item>
</shiporder>
</root>

Code Snippet

<xs:complexType name="roottype">
<xs:sequence>
<xs:element name="shiporder" type="shipordertype"/>
</xs:sequence>
</xs:complexType>

<xs:element name="root" type="roottype"/>

|||

Hey JayH,

That was it. Thanks a lot.

How to import Pervasive Database into SQL Server 2005

Hi

I have tried to import Pervasive Database to SQL Server 2005.

I am not getting any option in Data Source for Pervasive So I tried following

I have Provided the following Connection String to SQL server Import and Export Wizard (in .net framework data provider for ODBC)

Connectio String: Dsn=DEMODATA;Driver={Pervasive ODBC Engine Interface}

DSN: DEMODATA

Driver:Pervasive ODBC Engine Interface

but i m getting error that "Can not get supported data types from th database connection."

Can you please look in to it and tell me possible ways by which I can import pervasive Database into SQL server 2005.

and let me know if there is any other tool by which we can see "Pervasive ODBC Engine Interface"

in Data Source

Thanks,

Arun

Unless there is another Pervasive user out there who can help you, I would suggest the following: try building the package in the designer rather than through the wizard. The designer in Business Intelligence Development Studio offers more options than the wizard, which only covers simple scenarios.

Having said that - if the provider does not return data type information, it may yet be a problem.

Donald

|||Did you ever get this resolved?|||

I am Currently working in a ETL project that extracts data from Pervasive V8 DB. I wanted to use OLE DB connection but I could not make it work; so I am using ODBC. I installed the ODBC driver in my the development stations and the SSIS server (as a part of the Client tool Pervasive.SQL Data Manager installation); then I defined the Dsn. Whitin SSIS I defined my datasaource as:

Provider: .Net Providers\Odbc Data Provider

Use user or System data SOurce Name: <Choose from dropdown list my dsn>

The conction string of the data source is just simple: Dsn=<MyDsn>

And I was ready to go...

Then I had problems in the server because it is x64 bit machine; but that is a different story.

Rafael Salas

|||

I'm on Pervasive 2000i... my issue is that I'm trying to use the demodata dsn and I get the following error:

Cannot get the supported data types from the database connection "Dsn=demodata;Driver={Pervasive ODBC Engine Interface}".

Additional information:

--> Specified cast is not valid. (System.Data)

|||

German,

Did you try what Donald Suggested you? All what I did was inside of BIDS and actually I never tried with the wizard since I needed to perform some transformations before writing into the destination table

Rafael Salas

How to import Pervasive Database into SQL Server 2005

Hi

I have tried to import Pervasive Database to SQL Server 2005.

I am not getting any option in Data Source for Pervasive So I tried following

I have Provided the following Connection String to SQL server Import and Export Wizard (in .net framework data provider for ODBC)

Connectio String: Dsn=DEMODATA;Driver={Pervasive ODBC Engine Interface}

DSN: DEMODATA

Driver:Pervasive ODBC Engine Interface

but i m getting error that "Can not get supported data types from th database connection."

Can you please look in to it and tell me possible ways by which I can import pervasive Database into SQL server 2005.

and let me know if there is any other tool by which we can see "Pervasive ODBC Engine Interface"

in Data Source

Thanks,

Arun

Unless there is another Pervasive user out there who can help you, I would suggest the following: try building the package in the designer rather than through the wizard. The designer in Business Intelligence Development Studio offers more options than the wizard, which only covers simple scenarios.

Having said that - if the provider does not return data type information, it may yet be a problem.

Donald

|||Did you ever get this resolved?|||

I am Currently working in a ETL project that extracts data from Pervasive V8 DB. I wanted to use OLE DB connection but I could not make it work; so I am using ODBC. I installed the ODBC driver in my the development stations and the SSIS server (as a part of the Client tool Pervasive.SQL Data Manager installation); then I defined the Dsn. Whitin SSIS I defined my datasaource as:

Provider: .Net Providers\Odbc Data Provider

Use user or System data SOurce Name: <Choose from dropdown list my dsn>

The conction string of the data source is just simple: Dsn=<MyDsn>

And I was ready to go...

Then I had problems in the server because it is x64 bit machine; but that is a different story.

Rafael Salas

|||

I'm on Pervasive 2000i... my issue is that I'm trying to use the demodata dsn and I get the following error:

Cannot get the supported data types from the database connection "Dsn=demodata;Driver={Pervasive ODBC Engine Interface}".

Additional information:

--> Specified cast is not valid. (System.Data)

|||

German,

Did you try what Donald Suggested you? All what I did was inside of BIDS and actually I never tried with the wizard since I needed to perform some transformations before writing into the destination table

Rafael Salas

How to Import Data from AS400 files to Sql 2005 tables?

Hi All,

I want to Import data from AS400 to Sql2005. From the Sql Management studio I invoke the Import Data wizard. For the source I connect to the ISereis system and for the Destination I select the Sql2005 Database , When I go to the next step i.e select source tables I get the error
"An error has occured which the SQL Server Integration Wizard was not prepared to handle
No error message available,result codeB_E_CANTCANCEL(0*80040E15).(System.Data)" .After this I am unable to proceed further. I am using client Access tool to connect to ISereis

IBM DB2 UDB for isereis IBMDA400 OLE DB Provider

Regds,

Anu

If this is going to be a regular task, might as well dump your AS400 data in a delimited text file and import from there|||Moving to the SSIS forums