I have had no problems using execute sql task to populate a variable. How do you have it configured?
Also in your data flow task you can instead place a Row Count Transformation between your source and destination items to give you a row count of items transferred.