March 5, 2010 at 2:36 pm
i am trying to get list of records for a Ano and the query in my execute sql task is
select Aname from A where Ano= '" + @[User::ANo] + "'
i have declared two variable ano as string and resultset as object.
it executes but no rows are returned but there are rows for that Ano in the oracle database.
i have done an oledb connection to oracle database.
Can anyone suggest what i am doing wrong?
March 8, 2010 at 8:19 am
You can't reference a variable in an exec sql task like that.
Depending on your data source (not all providers support parameters) you will need to code it like:
SQL OLE DB connection type:
select Aname from A where Ano= ?
Click on Parameters then click Add.
Select the variable @[User::ANo], Input, give it a type, paramName = 0, this is actually param position (OLE only) and size -1.
ADO.Net connection type:
select Aname from A where Ano= @ano
Click on Parameters then click Add.
Select the variable @[User::ANo], Input, give it a type, paramName = @ano and size -1.
Now if the provider does not support parameters (such as pervasive) you can set up a variable such as Aquery as a string then in a script task change the query to add in the curren value of @[User::ANo] the script would be somethnig like
dts.variables("Aquery").value = "select Aname from A where Ano = '" + dts.variables("ANo").value.tostring + "'"
Make sure to add Ano and Aquery to the variables collection in the script task interface under the read only or read/write you dont need the varaible namespace just enter it as Ano,Aquery.
The in the exec sql task choose command from variable and pick Aquery.
Also for debugging I always use a script task with the code MSGBOX("The value of xxxxx is: " + dts.variables("myvar").value.tostring). This is a quick easy way to show the value of variable.
hth - Tom
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply