A quick note this week: this may be a known issue for some of you, I have only got to know this little trick the other day when I had to import data from an Oracle database to a SQL Server database (believe it or not – I did not have to use an Oracle database as a source up until now…). There were quite a few tables involved and a considerable amount of data to be moved, so the obvious solution was to use SSIS for this task. If you want to use the Microsoft provider for Oracle you have to install an Oracle client on the machine that runs your SSIS packages, so that was pretty easy to accomplish and once the client was installed we started to configure the ole-db source adapter. The data was a mix of numbers and character-type data and the next message that popped up the minute I configured the source was: “Cannot retrieve the column code page info from the OLEDB provider. If the component supports the “DefaultCodePage” property, the code page from that property will be used…”. Needless to say, when you hook this into a SQL Server ole-db destination and try to run the package, it does not run and throws this error again.
So, what to do? You need to change the “AlwaysUseDefaultCodePage” property of the Oracle ole-db source to True. This way the component will indeed use the machine’s default code page and things should go smooth from this point onwards.
(This is post number 12 for week number 11 of my SQL blog challenge)