ForLoop to check any row is present in the source table
I think the Oracle side returns a bigger than Int32 value because the table van hold more rows than fit into Int32, thus either consider using the DT_I8 (an equivalent of big int in SQL Server) or change the datatype returned in the query from Oracle to match Int32 specs: four-byte signed integer.Arthur My Blog
May 23rd, 2012 11:44am

Thanks for the wonderful suggestion. I'm facing a issue. The package is throwing an error [Execute SQL Task] Error: An error occurred while assigning a value to variable "Rowcount": "The type of the value being assigned to variable "User::Rowcount" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ". @Rowcount is Int32. If I point to MS SQL server database, it is working fine, but not with Oracle. Please suggest what should be the data type I should set for the @Rowcount variable or in the ExecuteSQL task SQL query what casting I should do so that it works for oracle. I tried few but not succeeded Please help.
Free Windows Admin Tool Kit Click here and download it now
May 26th, 2012 10:41am

I think the Oracle side returns a bigger than Int32 value because the table van hold more rows than fit into Int32, thus either consider using the DT_I8 (an equivalent of big int in SQL Server) or change the datatype returned in the query from Oracle to match Int32 specs: four-byte signed integer.Arthur My Blog
May 26th, 2012 11:50am

Hi Jim, I think that an Int64 will fit what Oracle returns for the rowcount. What is the actual number that Oracle returns ? Regards Rgis
Free Windows Admin Tool Kit Click here and download it now
May 26th, 2012 12:40pm

Hi, I'm using a ExecuteSQL Task within a ForLoop. I need the ForLoop to keep on executing till any row is returned by the ExecuteSQL task. The query in ExecuteSQL task is Select Count(*) from Table1. For this I have created a UserVariable @RowCount. In ExecuteSQL the Resultset is set to @RowCount. In the ForLoop, EvalExpression is @Rowcount>0 and InitExpression, AssignExpressoin are not set. So that ForLoop is success when some rows are present in the table1 and ForLoop will keep on executing when no row is present in the table1. Now, even there is no row in the table, ForLoop is successful. I'm not understanding the behavior and how to resolve same. Please help or suggest some pointers. Thanks Regards Jim
May 27th, 2012 5:51am

Hi, you need to set EvalExpression @Rowcount = 0 , InitExpression @Rowcount == 0. This will cause the loop to run until @rowcount is not 0. And remember to have the result set of Select count(*) from table 1 setting the @rowcount variable. Or have a look at that article : http://bidn.com/blogs/MikeDavis/ssis/158/make-an-ssis-package-delay-or-wait-for-data Hope that helps Regards Rgis
Free Windows Admin Tool Kit Click here and download it now
May 27th, 2012 6:06am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics