pandkyon
asked on
Unable to pull Oracle data into SQL Server using SSIS with parameters - Oracle OLE DB provider
First of all, I do not have access to SSDT or SSIS in SSMS. I use the 32 bit import / export wizard tool for creating SSIS packages.
I have several SSIS packages (one of which is attached as .txt) where I need to pull data from Oracle and insert it into SQL Server tables.
The query in the package, are quite simple:
Select * from table1 where submissiondate = ?
The problem is passing a parameter into the ORACLE query as a variable using SSIS. I now understand that this is a documented issue (OLE DB ?) and there a workaround:
Workaround:
stackoverflow.com/question
Description:
1.Create a package variable
2.Double click on the package variable name. (This allows you to access the properties of the variable)
3.Set the property 'EvaluateAsExpression' to true
4.Enter the query in the expression builder.
5.Set the OLE DB source query to SQL Command from Variable
I cannot get passed step two above. EvaluateAsExpression, was not found in the package that was created (attached).
Please advise how to fix this problem using the attached file.
thanks in advance:
Paul
UPDATE-TABLE.TXT
I have several SSIS packages (one of which is attached as .txt) where I need to pull data from Oracle and insert it into SQL Server tables.
The query in the package, are quite simple:
Select * from table1 where submissiondate = ?
The problem is passing a parameter into the ORACLE query as a variable using SSIS. I now understand that this is a documented issue (OLE DB ?) and there a workaround:
Workaround:
stackoverflow.com/question
Description:
1.Create a package variable
2.Double click on the package variable name. (This allows you to access the properties of the variable)
3.Set the property 'EvaluateAsExpression' to true
4.Enter the query in the expression builder.
5.Set the OLE DB source query to SQL Command from Variable
I cannot get passed step two above. EvaluateAsExpression, was not found in the package that was created (attached).
Please advise how to fix this problem using the attached file.
thanks in advance:
Paul
UPDATE-TABLE.TXT
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
For Suggestion above 1: I do not have SSDT or SSIS tools so I cannot see the properties of the package other than notepad.
Results:
For Suggestion 2 above:
SSIS ERROR CODE DTS_E_OLEDBERROR. An OLE DB error has occurred. Error Code: 0x80040E51.
an Ole DB record is available. .......Provider cannot derive parameter information and set parameter info has not been called.
Any other suggestions?