I currently have the very humble priveledge of working with Oracle as a data source on an SQL Server 2005 Integration Services (SSIS) project.
What the hell are you doing working on Data Warehouse ETL stuff, Rick, you ask- that's a good question (Jack of all trades, master of some as they say), but let's not digress.
The product I am involved in can be deployed either in and Oracle or SQL environment, along with each permutation therein for a total of 4 possible configurations. So, SSIS we thought, would be the perfect choice for handling some non-trivial ETL chores. There are a few things I've stumbled on, and I will document them here, both for posterity and for the benefit of anyone else.
|
Issue
|
Description
|
Solution
|
| ORA-00923: FROM keyword not found where expected |
Brackets in Oracle are like, not cool man. i.e. SELECT [Cost] FROM Table |
Get out your surgical gloves and sanitize that T-SQL. Remove brackets, dbo. qualifiers, etc (the latter don't make sense in Oracle anyway). |
| SSIS: Cannot retrieve the column code page info from the OLE DB provider. |
This is due to a discrepancy in the way Oracle handles character encoding. |
Set AlwaysUseDefaultCodePage to true in the OLE DB Source task. Thanks to Jamie for this one. |
| ORA-00936: missing expression |
Oh this is brilliantly informative. It looks like Oracle has a problem with the CONVERT function. For example: CONVERT(varchar(10), VI.START_DATE, 101) AS START_DATE, |
The problem is that in Oracle, there is no CONVERT function that takes a given expression and converts it to the requested datatype. There is a TO_CHAR function, but this is not a syntax dialect portable option. The solution in this case was to omit the CONVERT, and instead operate on the DATETIME field natively dealing with the truncation of time later in the ETL pipeline. |
| ORA-00905: missing keyword |
Oracle doesn't like "AS Field" alias notation in a FROM clause. For example, the following code wiill not parse:
SELECT * FROM TABLE AS MyTable
|
The solution here is to simply omit the "AS" keyword:
SELECT * FROM TABLE MyTable
If only they were all this easy.
|
| SSIS: One or more columns do not have supported data types, or their data types do not match. |
When source records funnel into SSIS, the engine translates the native data types into SSIS datatypes. Sometimes, the infered datatype doesn't match with the target within the Lookup trandofrmation or OLE DB target. |
Right click the OLE DB Source and select "Show Advanced Editor". Click the "Input and Output Columns" tab and change the inferred datatype, saving the properties. |
| ORA-03291: Invalid truncate option - missing STORAGE keyword |
When executing multiple statements in a batch, SQL allows you to add each statement on a line and terminate the statements with a "GO" statement. GO is not valid in Oracle and it appears that you have to terminate each statement with a "/" |
So in SQL, you coud just do this:
TRUNCATE TABLE Table1
TRUNCATE TABLE Table2
TRUNCATE TABLE Table3
TRUNCATE TABLE Table4
GO
In Oracle, add a "/" after each statement in the "batch":
TRUNCATE TABLE Table1
/
TRUNCATE TABLE Table2
/
TRUNCATE TABLE Table3
/
TRUNCATE TABLE Table4
/
OR
TRUNCATE TABLE Table1;
TRUNCATE TABLE Table2;
TRUNCATE TABLE Table3;
TRUNCATE TABLE Table4;
However, none of these seem to work in an Execute SQL Tasks.
|
| |
|
|
| |
|
|