How do I get Oracle 32 bit ODBC to work with SSIS BIDS 2008?
I am utilizing VS2008 and SQL Server 2008. Specifically I have created a number of SSIS packages that utillize a third party connection to an Oracle database. My issue is that I am trying to convert these packages to utilize the Oracle ODBC drivers (trying 32 bit first then 64 bit). In the Connection Manager I have created a connection that test connects successfully. I have added a "ADO Net Source" an assigned the previous connection manager to it. I get the listing of tables from the Oracle database but I get an error when I attempt to preview. Also, no columns are shown when I try to view them. I do currently have both 32 bit and 64 bit drivers installed; I have installed the Oracle Client; I can run the Oracle 32bit utility to make connection and view the table no problem; I have changed the Run64BitRuntime option. I know that this cannot be that difficult as I see lots of articles where people have it running just not how it was done. Any information on what I might be doing wrong would be greatly appreciated. Thanks, Andrew
October 27th, 2010 3:21pm
OK, some progression. I have skipped selecting the table and went with the "SQL Command" option. This does bring back the data from the database. My new issue is that I prevously moved to a look up and compared to see if the primary key already existed. The issue is that the data types apparently are not the same and while the returned data type from the Oracle ODBC seems to be DT_WSTR I think that the OLEDB for the SQLversion table is DT_STR. If I go into Advanced Editor for the Source box, I am not allowed to change the Output column datatypes ... so on to this issue.
October 27th, 2010 7:45pm
... and that problem is done. Apparently with ADO NET Source the values are all Unicode where as the table was defined with non-unicode fields. So either I need to do a Data Conversion task or change the receiving table to Unicode. If there is anything else on this issue I will continue to post it.
October 27th, 2010 8:16pm
You will need to do a data conversion...
October 27th, 2010 9:29pm