SQLBulkCopy for bulk move of data

Question

Hi All,

Im trying to move data in bulk from oracle to sql server. Both source and destination table has the same structure. One of the field is msgtimestamp and the rest are number and varchar field.

Im getting the following error when bulkcopy.WriteToServer is called for doing bulk move "The given ColumnMapping does not match up with any column in the source or destination"

I tried putting column names of source and dest exactly as field names wondering if its case sensitive but did not work.

Can someone help me out....

Thanks in advance.

Regards, Soja

Answer 1

DateTime fields have different precision between Oracle and SQL Server; oracle can store billionth of a second.) Depending on how you are doing your transfer you likely want to do this in one of two ways.

if you are using openquery to get the data back from the oracle machine through a linked server then you can use The PL_SQL function TO_CHAR will handle the conversion easily enough.

select * from openquery(MyLinkedServer, 'select TO_CHAR(systimestamp, ''YYYY-MM-DD HH24:MI:SSXFF3'') from dual')

If you are doing this through DTS or SSIS I would create a view on the oracle machine that converts the column there and run the import script against the view.