ODP.NET

Hi Guys,

I have a VB.Net application that queries an Oracle database and then uses the sqlbulkcopy to move it to a Sql table. It works fine without column mapping. As soon as I try to map the columns it fails. Basically says that columns do not match.

This works ...

Using cmd As New OracleCommand(dSource.SQLStatement, dSource.OracleConnection)
cmd.CommandTimeout = 60
If cmd.CommandText.Contains("@ERProjectCode") Then cmd.Parameters.AddWithValue("@ERProjectCode", Job.Project)
cmd.Connection.Open()
Using dr As OracleDataReader = cmd.ExecuteReader()
Using s As New SqlBulkCopy(destConn, SqlBulkCopyOptions.Default, bcpTransaction)

s.DestinationTableName = Job.ApplicationDestinationTable
s.WriteToServer(dr)
s.Close()
End Using
End Using
cmd.Connection.Close()
End Using

This Fails ....

Using cmd As New OracleCommand(dSource.SQLStatement, dSource.OracleConnection)
cmd.CommandTimeout = 60
If cmd.CommandText.Contains("@ERProjectCode") Then cmd.Parameters.AddWithValue("@ERProjectCode", Job.Project)
cmd.Connection.Open()
Using dr As OracleDataReader = cmd.ExecuteReader()
Using s As New SqlBulkCopy(destConn, SqlBulkCopyOptions.Default, bcpTransaction)

'*****COLUMNS MAPPING *****
For i As Integer = 0 To dr.FieldCount - 1
s.ColumnMappings.Add(dr.GetName(i), dr.GetName(i))
Next i

s.DestinationTableName = Job.ApplicationDestinationTable
s.WriteToServer(dr)
s.Close()
End Using
End Using
cmd.Connection.Close()
End Using

I have tried casting in the Oracle Query file but nothing seems to work.

I have run out of ideas and welcome any suggestions.

Thank You !!!