February 2, 2006 at 8:01 pm
I hope someone can point me in the right direction on this...I have setup a package to pull data in a table that is in an Oracle Database to a table in a SQL Server database. My problem is I am trying to setup an Execute SQL Task that will run before the Data Flow task and verify the record count is > 0.
I created the Execute SQL Task and am trying to populate a variable of type Int32 with the result of the query. When I try and execute the task I am getting the following error regardless of the variable's data type
[Execute SQL Task] Error: An error occurred while assigning a value to variable "StateCount": "Unsupported data type on result set binding StateCount.".
I have followed the example on SQLIS.com and believe I have things setup correctly(I.E. the query returns the count as StateCount which is the name of the variable as well)
Any ideas or help would be greatly appreciated!
Erich
February 9, 2006 at 8:39 am
Hi,
I have also experienced these same problems. The only solution that has worked for me is to use the oracle TO_CHAR function in your select statement and set the result set parameter to be a string variable.
Select Statement...
SELECT TO_CHAR(COUNT(DocID)) AS RECORDCOUNT FROM Documents WHERE.....
In the execute sql task set up a single row result set, and in the result set section, set up a variable named RECORDCOUNT of type string.
This works but you'll have to convert you strings back to numbers in a script task.
Rich
February 9, 2006 at 8:43 am
Thanks Rich.
I did some poking around in Oracle datatypes and found you can also select the count back as a numeric datatype. Numeric datatype in Oracle will match to the Int32 datatype in SQL.
This worked for me...
SELECT Cast(COUNT(*) as Numeric) from <Tablename>
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply