SSIS:: Pass Parameters to Oracle in DataFlow task

Todd C

2008-10-31 12:56:01 UTC

Try this:
Remove your EvaluateAsExpression stuff set the initial value of the
commandSQL variable to somthing that can be parsed by the Source:

SELECT fname,lname,city FROM details WHERE
TO_CHAR(Trxdate,''mm/dd/yyyy'') >= '01/01/1990' and
TO_CHAR(Trxdate,''mm/dd/yyyy'') <= '12/31/1990'

Test that out and you will probably be OK, even if you don't return any
records.

Now, put in a Script Task ahead of the Data Flow and in it use the REPLACE
function to substitute the '01/01/1990' literal string with the value of the
first date variable. Same for '12/31/1990' and the second.

Put a BreakPoint on the OnPostExecute of this Script Task and examine the
contents of the commandSQL variable to see if it is what you expect at
runtime.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

Post by msnews.microsoft.com
Hello,
Iam trying to pass parameters to Oracle via SSIS data flow task but Iam
1. Declare three variables in SSIS - user::Commandsql (having
EvaluateAsExpression=true ), user::date1 and user::date2
2. Store the below statement in user::Commandsql
'SELECT fname,lname,city FROM details WHERE
TO_CHAR(Trxdate,''mm/dd/yyyy'') >= '+[user::date1]+ ' and
TO_CHAR(Trxdate,''mm/dd/yyyy'') <= '+[user::date2]
3.Set the default values for user::date1 as 3/27/2007 and user::date2 as
4/27/2008
4. In data flow task source editor, I connect to my Oracle instance and
select "sql command from variable" as data access mode. Then select
'user::Commandsql' as the variable.
TITLE: Microsoft Visual Studio
------------------------------
There was an error displaying the preview.
------------------------------
ORA-00900: invalid SQL statement
(Microsoft OLE DB Provider for Oracle)
------------------------------
OK
------------------------------
When I just execute the statement without giving pamaters and give the dates
directly, its working fine. What am I missing here? Its been three days
since Iam stuck with this.
TIA,
Abba