For each loop can we avoid?? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

For each loop can we avoid??

Author

Topic

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2011-12-27 : 07:01:28

Best way to do??

i have excel out of which i am getting max date and compare it with DB table date and then decides whather to follow updation loop in package or not.

I did like

Execute Sql task
1.Result Set - Full
2.Connection Type- Excel
3.Sql Statment- Select max(date) from [Sheet1$]
4.Result Set - Result Name - 0 Variable name-User::MaxDat (Variable MaxDat is Object data type)

Passing it to for each loop
1.Enumerator - Foreach ADO Enumerator
2.ADO Object Source variable - MaxDat
3.Variable - User::MaxDat Index - 0

in for each loop
Script Task
1.Read Only Variable - User::MaxDat
2.ReadWrite variable - User::MaxDat_Out (MaxDat_Out variable data type as String)

next task is
Execute Sql task
1.Result set - Single row
2.connection type - OLEDB
3.Sql Statement - Select count(*) ROW_CNT from Excel_Sheet where (QDate = ?)
4.Parameter Mapping- Variable name user::MaxDat_Out Direction - input Datatype - Date Parameter Name 0..parameter size -1
5.Result Set- Result Name Row_CNT Variable name - user::ifthere (variable ifthere data type as int)

T.I.A

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-27 : 08:18:05

shouldnt the execute sql task be inside loop as well?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2011-12-28 : 01:30:34

last Execute SQL task is outside For each loop..only script task is inside loop...

T.I.A

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-28 : 01:42:13

why? should it iterate for each of files in loop for getting max date in them?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2011-12-28 : 02:45:07

Hi,

I am calculating max date for this excel in first task only. And then it got assigned as Object in Loop..after that i am retrieving this object as Date format and passing it to next execute sql task as variable...my question is can i avoid loop for this..means get max date as variable..then pass this variable to last execute sql task and get records from DB....

T.I.A

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-28 : 03:31:40

hmm...why do you need object to store a date? its a single value for excel isnt it which is its maxdate?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources