get recordcount from Oracle and update sql server table

I posted this question a little while ago but was not able to implement it. Now I am back to the same issue. Basically I want to get a recordcount from a table in Oracle and update an existing record in sql server with the value.

I am trying to accomplish this using a Execute SQL Task. In this task I am pointing to a Oracle DB that I am able to query from SSIS so connectivity is not an issue.

I have defined a variable EmpRC of type int32.

I have a following the the SQL Task:

query: select count(*) from emp;

result set=single row.

and on result set tab ResultName =0 and variable name is same defined above : User::EmpRC

I get an error when I run this:

[Execute SQL Task] Error: An error occurred while assigning a value to variable "EmpCompRC": "Unsupported data type on result set binding 0.".

I have tried using different data types for EmpRC but having no luck. any ideas?

Not sure if this is a typo, but you keep refering to variable EmpRC, yet the error from ssis refers to a variable EmpCompRC.

Also, why don't you try using that sql statement in a data flow task to see what data type is assigned to it.

|||

Anthony Martin wrote:

Not sure if this is a typo, but you keep refering to variable EmpRC, yet the error from ssis refers to a variable EmpCompRC.

Also, why don't you try using that sql statement in a data flow task to see what data type is assigned to it.

Oracle doesn't have "integer" data types, that's why it doesn't work. Oracle only has NUMERIC data types, and when used without a precision, it is to be considered an "integer." It's a pain in the a$$ and I don't think Microsoft has any intentions of fixing the problem:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=264932

[Microsoft follow-up]|||

so is there any workaround? I am able to see the link but when click on view workaround it takes me to my msdn profile.

|||

Shahab03 wrote:

so is there any workaround? I am able to see the link but when click on view workaround it takes me to my msdn profile.

This is the referenced link in the workaround section of that Connect article:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PageIndex=2&SiteID=1&PostID=107027

I don't think it will help in the end though.
|||

that is a disappointment.

|||

well I figured out how to accomplish this. So hopefully everyone else wont have to bang their head on the 'wall of data types'.

1. well basically you will create a variable in SSIS of data type String.

2. you will have to convert the returning data type of select count(*) to a character in oracle. e.g.

select to_char(count(*)) from emp

3. create another SQL Task after the SQL Task for "count(*)". and in the parameter tab set the datatype to varchar with variable same as ResultSetName from previous SQL Task.

hope this is clear enough.