March 19, 2008 at 4:42 am
Hi Friends,
I have a small problem in parameter mapping for Execute SQL Task.
I am using a delete statement with 2 conditions.
Followed by another Execute SQL Task which contains commit statement.
delete from tname where c1 = ? and c2 =?
where c1 is number(4) datatype and c2 is of varchar2(20) datatype in oracle.
The connection manager i am using is ORacle OLE DB provider.
I am passing 2 global variables i.e g_v1 of Int32 and g_v2 of String Type.
In the parameter mapping of the Executing SQL task, i am mapping these 2 variables for
c1 and c2 and changed the datatypes inside parameter mapping as Numeric for c1 and Varchar for c2.
I also set the property as ByPassPrepare = True.
When i am executing the package i getting INVALID NUMBER ERROR.
i believe the SSIS is unable to perform the implict datatype converison.
For the next run, i changed the g_v1 varible datatype to Double and also i changed the parameter mapping for c1 as Doble datatype.
This time it is working fine. I can see the Green signal for the 2 SQL Tasks.
But when i connected to Oracle check the count in the table, the data is not getting deleted.
Also,
I set the property RetainSameConnection = TRUE for oracle connection manager.
I am not able to trace this logical error.
The same is working fine in my local machine.
But i am facing the problem when i deployed the same on the client machine.
Is there any problem with parameter mapping?
What should be equialent Datatype for Oracle NUMBER datatype that should be used inside the SSIS package while declaring the global variable and
inside the parameter mapping.
Is there any way to see/look the sql statement which is formed after Parameter Subsititution inside the log file?
Can we print the SQL Statement Formed by the Execute SQL task inside a script task ?
Any help would be greatly appreciated !
Thanks in advance
March 19, 2008 at 12:42 pm
The oracle OLEDB drivers tend not to work all that well with parameters in SSIS. Blame anyone you want, but they should be avoided.
Rather than using parameters, build the SQL statement you want to run using either an expression variable (preferred method) or using expressions on the component in SSIS.
If you use expression variables, make sure your variable is in the appropriate scope - the expression is evaluated when the variable comes into scope, not when the variable is used.
March 20, 2008 at 1:30 am
I solved it using Propert Expressions.
Thanks!
August 13, 2008 at 11:53 am
I am getting a similar error. What types of changes did you make to the property expression to get it to work?
June 24, 2009 at 1:51 pm
I am also getting a similar error. Can you please post what types of changes did you make to the property expression to get it to work?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply