Hello,

Iam trying to pass parameters to Oracle via SSIS data flow task but Iam
getting "Invalid SQL command" Oracle error. This is what I have done:

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.

When I click 'preview', its giving me this error:

TITLE: Microsoft Visual Studio
------------------------------

There was an error displaying the preview.

------------------------------
ADDITIONAL INFORMATION:

ORA-00900: invalid SQL statement
(Microsoft OLE DB Provider for Oracle)

------------------------------
BUTTONS:

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

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]


"msnews.microsoft.com" wrote:

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]


"msnews.microsoft.com" wrote:

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]


"msnews.microsoft.com" wrote:

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]


"msnews.microsoft.com" wrote:

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]


"msnews.microsoft.com" wrote:

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]


"msnews.microsoft.com" wrote:

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]


"msnews.microsoft.com" wrote:

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]


"msnews.microsoft.com" wrote: