ForLoop to check any row is present in the source table

Question

ForLoop to check any row is present in the source table

Jim1234

SSC Eights!

Points: 889
More actions
May 23, 2012 at 3:48 am

#261889

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Jim

You need to do more than set the value of a variable in T-SQL. You need to get it into an SSIS variable. The best way of doing that is with a query like SELECT ? = COUNT(*) FROM Table1 (better still, create a stored procedure to return the row count from the table with an output parameter) and parameter mapping in your Execute SQL task.

John

Jim1234 SSC Eights! Points: 889 More actions · Answer 2

Thanks John,

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.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

I'm afraid I've never worked with Oracle. Is there anything in the Oracle syntax that allows an explicit conversion to an Int32-equivalent type?

John

Jim1234 SSC Eights! Points: 889 More actions · Answer 4

I tried select Cast(count(*) as int) from crc.crc_ds1_td. It works in oracle by ExecuteSql task is throwing the same error 🙁

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 5

Jim1234 (5/23/2012)
I tried select Cast(count(*) as int) from crc.crc_ds1_td. It works in oracle by ExecuteSql task is throwing the same error 🙁

I don't know if it'll fix your particular problem but you must use all caps for Oracle objects. It's just the nature of the beasts.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jim1234 SSC Eights! Points: 889 More actions · Answer 6

Hi,

The problem is fixed. I have used @RowCount==(DT_STR,50,1252)"0" in the EvalExpression in Forloop and it is working.

I have a request. Is it possible to kill the SSIS package at runtime.

Scenario,

Package starts at 5.00 AM. Checking for availability of data and if not available ForLoop continues to execute. But if the data is not available after 3hrs of package execution then the package must be killed.

Please let me know how to implement this.

Thanks

Regards

Jim

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

Jim

Connect to the Integration Services server in SSMS, expand the list of running packages, and stop the one that is over-running.

Edit: that's not the best way of doing it on a regular basis. What you need is to break out of the For Loop. Put another condition in the loop, checking that the difference between the current time and ContainerStartTime is less than three hours.

John

Jim1234 SSC Eights! Points: 889 More actions · Answer 8

Hi John,

The package execution should be stopped automatically. Looking for a solution which could be implemented inside the package or the package is schedule in SQLServer agent by the DBA. Is there a way in Agent to schedule it automatically to kill a package which is running for more than 3 hrs and send a mail to inform the same.

Regards

Jim