Oralce Distination

Hello Everyone,

I’m doing a type 2 SCD, Flat File as my source and oracle table as my destination but there is a problem in SCD component. I'm getting error.

is SSIS accessible to oracle as destination if yes please do inform me how do i resolve this issue.

Thank you

What is the error message? Please post that as without it we cannot help you.

An Oracle destination using OLE drivers will work just fine -- for the most part.

|||

The Error Message

TITLE: Microsoft Visual Studio

Error at Data Flow Task [OLE DB Command 1 [2007]]: An OLE DB error has occurred. Error code: 0x80040E51.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".

Error at Data Flow Task [OLE DB Command 1 [2007]]: Unable to retrieve destination column descriptions from the parameters of the SQL command.

Warning at {CF5DCB64-279E-45A4-A9A8-FF2FBB130980} [Insert Destination [1972]]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.

ADDITIONAL INFORMATION:

Errors were encountered while generating the wizard results:
Error at Data Flow Task [OLE DB Command [1996]]: An OLE DB error has occurred. Error code: 0x80040E51.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".

Error at Data Flow Task [OLE DB Command [1996]]: Unable to retrieve destination column descriptions from the parameters of the SQL command.

Error at Data Flow Task [OLE DB Command 1 [2007]]: An OLE DB error has occurred. Error code: 0x80040E51.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".

Error at Data Flow Task [OLE DB Command 1 [2007]]: Unable to retrieve destination column descriptions from the parameters of the SQL command.

i had try to resolve the problem thru adding the derive parameter information.

but still i'm gettting an data conversation problem.

my source is a flat file and source column datatype is string but when i added the parameters its taking as unicode datatype can you help me in this.

thank you

Oracle OLEDB drivers problem with Numbers

Hi All:

I am using oracle oledb drivers to write to a oledb destination.

if i give decimal values to decimal fields in the source table, i get the same in destination. But if the input is integers, in some cases, the value in the destination is different from that of source

Source Target

50 50.00
100 0.000
111 111.000
600 0.0000
520 20.00
178 178
4546.50 4546.50

I have Sql server SP2 9.0.3042 installed on my machine. Please let me know if theres something i am missing out.

Thanks,

Vipul

There is no such thing as an integer in Oracle. It's a NUMERIC(p,0) field. That is, it has no scale. SSIS doesn't support this at the moment. Instead, write your query such that you convert the "integer" field into a NUMERIC(p+1,1) field, or something like that. Then map to a decimal field in SSIS. From there, if you want integers out of the data, use a derived column to cast the values to integers.|||

Let me put it this way phil. Have you come across decimal data being changed from source to target without any transforms in between ? I was not correct in putting the question but the cause of my concern is that if the source has 500 how the target is getting it as 0.

Is there some problem in SSIS for this or this is oracle oledb driver problem?

|||Have you looked at the data with a data viewer to see what is contained there? You might need to recreate the OLE DB source.|||

ya i have viewed data with the data viewer before the oledb destination. Data is fine till data viewer. Theres something happening in oledb destination and thats why i suspect the drivers.

Also, the same behaviour is not happening on one of my other machine. The machine confguration of both the machine are same. I am executing the same pacakge from both the machines.

The version of software on both mahcine are:

-

SQL Server sp2 9.0.3042

Oracle 10g

Let me know your thoughts on this..

|||Is your destination SQL Server?

Have you looked at the advanced properties of the OLE DB Destination to ensure that the data types for all of the columns are correct?|||

Destination is Oracle.

And i have checked all the datatypes as per ur suggestion but still the problem exists.

|||I'm going to have to bow out as I don't have an Oracle instance to test with.