ODBC Destination in SSIS

I read that Integration Services does not come with built-in support for ODBC destinations, so I tried to write Script component as destination task.

My destination is odbc for oracle, and it failes with the message:

"Null password given, logon denied".

I can't understand it, I used with the same connection with datareader source and it works.

I spent a long time on this problem, please help me.

Thank you in advance

Noam

How do you create your connection object? Do you just take the connection string from the connection manager or call AcquireConnections on it? If you just take the connection string it will have the password filtered out.

Thanks.

|||

First of all, Thank you for your reply.

You are right, I called the AcquireConnections as you are see:

Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

Dim connectionString As String

connectionString = Me.Connections.Connection.ConnectionString

odbcConn = New OdbcConnection(connectionString)

odbcConn.Open()

End Sub

I changed it, and moved the initialization of the connection to the PreExecute() function.

Now the compilation is succeed, but it is failed at the run time with the same error "Null Password given, logon denied".

Please Help!

Noam

|||

No, as I said before you should not construct your own ODBC connection. Instead, call AcquireConnection on the connection manager and cast the returned object to IDbConnection.

Something Like this:

Dim connection As IDbConnection

connection = Me.Connections.Connection.AcquireConnection(null) as IDbConnection

HTH.

|||

I tried as you suggested.

Dim connection As IDbConnection

connection = Me.Connections.Connection.AcquireConnection(Nothing)

Now, I get This:

"Unable to cast COM object of type 'System.__ComObject' to interface type 'System.Data.IDbConnection'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{9BB226F4-2AF3-37E7-B91D-3BB936FC0A7E}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE))."

I'm stuck with this for 3 days (!!!).

Please your help!

Thanks in advance.

Noam

|||

Is the connection you created ADO.NET for ODBC or native ODBC?

You should use ADO.NET for ODBC.

Thanks.