I am using SSIS for table transfers between different DBs. Since it is not specific to one source and target, and SSIS does not have dynamic column mapping itself, I implemented a script on C# by utilizing the OracleBulkCopy and SqlBulkCopy for writing to the destination, which of course are used depending on the DB types. Correct me if I am wrong, but I think this would be the best performing solution, using the specific drivers provided directly by Oracle or Microsoft, right? Anyway, I am having a problem.
I am trying to copy a view to a table, both in Oracle. I'm not sure this error is what I think, but my guess is that the column types do not match properly. All the columns names match, so I don't have to do any mapping, but there might be some differences in types, for example I want to copy a VARCHAR to a NUMBER among others. The OracleColumnMapping works only for column names and not types from what I found.
So, am I right to assume that the type mismatch is the problem, and if yes, is there any way to set BulkCopy to force type casting to the destination table? It would be great if it could be set as an option to automatically type cast all columns, and not by mapping, but any solution please let me know.
P.S.: I think the same would work for SqlBulkCopy then too, but I also need a solution for that if not, although I haven't tried it yet on MSSQL.
Thanks to anyone who helps.
In my opinion, I find it easier to use SSIS Packages (.dtsx) to make transfers from a Oracle DB to a SQLServer DB. And the cast can be easily made by using the toolbox. C# is not needed for such operations.