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 Acquire Connection for ssis return null

Hi All!

I'm writing a custom component in c# for SSIS and I have a problem with AcquireConnection...

I wrote this code:

public override void AcquireConnections(object transaction)

{

if (ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager != null)

{

ConnectionManager cm = DtsConvert.ToConnectionManager(ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager);

ConnectionManagerAdoNet cmAdo = cm.InnerObject as ConnectionManagerAdoNet;

if (cmAdo == null)

throw new Exception("The ConnectionManager " + cm.Name + " is not an ADO connection.");

this.conn = cmAdo.AcquireConnection(transaction) as OracleConnection;

}

but the 'conn' is ALWAYS null...

I tried

this.conn = ((IDTSConnectionManagerDatabaseParameters90)cmAdo).GetConnectionForSchema() as OracleConnection;

too, but no result: the 'conn' is null again...

If I use oledbconnection or sqlconnection instead of oracleconnection the method works fine... I really don't understand

could you help me plz?

Using "as" will return NULL if the object can't be cast to the specified type. Is the connection manager actually an Oracle connection?|||

Yes, the connection manager is an Oracle Connection.
I use .Net Providers --> OracleClient Data Provider....

|||

I find the problem: I reference the class Oracle.DataAccess.Client and Oracle.DataAccess.Type and the 'conn' is null...

If I reference the System.Data.OracleClient all is right!

But I must use the Oracle.DataAccess... :-(

Has anybody use it yet?

|||

What is Oracle.DataAccess.Client? Is it an ADO.NET provider? If it is an ADO.NET provider you need to use an appropriate connection type that comes with it. Or you should be able to use generic connection (DbConnection) object.

HTH.

|||

Check the type of the object being returned, as there is some cinfusion as to what you have done here. Perhaps some code like -

object test = cmAdo.AcquireConnection(transaction);

Debug.Assert(false, test.Type.ToString());

I would also look at the ConnectionManagerType property for the connection, as that shoudl also tell you the type of ADo.Net connection you can expect back. See th example value below, showing that I used the MS ADO.Net oracl provider, and the class I know is System.Data.OracleClient.OracleConnection, found in the assemby System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089.

ADO.NETystem.Data.OracleClient.OracleConnection, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089

Perhaps you have not used a managed provider, but the .Net OleDb provider to connect to an Oracle OLE-DB provider. In which case your connection manager type would be -

ADO.NETystem.Data.OleDb.OleDbConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089