FOREACH LOOP CONTAINER

[JD1866DSC]

Hi, I want to update statistics against every table using the for each container. I've dragged the foreach object on to the control flow page and set the enumerator to be for each table and added a variable to capture the table name. I've then dragged the execute SQL task into the middle of the container and edited the SQL statement to UPDATE STATISTICS, then edited the parameter mapping option to contain the variable from the container.


Firstly is this possible? Secondly Is it the best way to accomplish this task? have I gone about this the correct way and how to I actually pass the paramter to UPDATE STATISTICS SQL statement?

Any help would be greatly appreciated

 

[mrdenny]

Why not just use the sp_MSforeachtable stored procedure and skip SSIS for this task.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[JD1866DSC]

Hi MrDenny,

Sounds Good, I presume this is an undocumented sp. I need it in the SSIS solution as its a step in one of the packages I need in the datawarehouse ETL. I'm guessing I could put this in a simple execute SQL task.
Have you an example how I would use it in this scenario?

Also do you know how I would use this in a for each container, as I'm keen to learn how to use this object

 

[mrdenny]

Sure, you use this code.

exec sp_MSforeachtable 'UPDATE STATISTICS ?'

I've only used a for each container once, and I don't remember what I needed to do to make it work, sorry.

I try to stick with T/SQL as much as possible as it's the native language.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[JD1866DSC]

Thanks MrDenny, thats fantastic.

 

[MDXer]

The for each table requires 2 variables. The first is a type object the second would be a type string (in this case). You would execute the following in an execute SQL task.

Select
Table_Name
From information_Schema.Tables

This return set you would use to populate thevariable of type object. In the For Each Loop you would use the the Object Type var as your source and use it to set the String type var. inside the loop you would have a task that you would use to update your stats passing the var of type string to the command.

I typically name these 2 variables with the object type being the plural of the string var.

Tables: Object
Table: String

The only reason I can think of to not use Denny's method is if you are doing detailed auditing of your DW load. Many DWs have detailed auditing to report how when and howlong various steps take. If you don't have auditing then this approach is overkill.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck