I am not a developer. I am SQL Admin. I want to write an SSIS package. The
package should go and query msdb databases of all corporate SQL servers for
backup status. I can create a table where I can store SQL server names of all
SQL servers. What I would like to know how in SSIS package I can dynamically
read the SQL server name and loop through from the table and make a
connection to each of the SQL server and query it. I want to store the query
result to some variable which I can use to update my reporting database.
I know it must be simple but since I don’t do development it could be hard.
Thx for any response on it.
Moh

--
Sr DBA
BC
mabbas (AT) brwncald (DOT) com

Hello Moh:

Well, for a rookie you certainly jumped right in with both feet! This one
will have a steep learning curve for you so design your package in stages,
testing each to make sure it is doing what you want. Use breakpoints to
examine variables and properties while the package is running.

1) Create a Variable with Package scope, type = Object. Create another
package scoped variable called ServerName, string type, with an initial value
that is valid for one of your servers.
2) Add an Execute SQL task with sql of "SELECT ServerName from MyServers".
Set the result set to Full Result Set. On the Result Set page, add a Result
set, named "0" and assign it to your Object variable.
3) Add to the Control flow a For-Each task. Connect it down stream of the
SQL task. Set the ForEach task enumerator on the second page to be an ADO
Enumerator. Under ADO Object, select the Object variable. On the Variable
Mappings page, set up a mapping to the SeverName variable, indexed as 0.
4) Inside the loop, create a data flow. Design the data flow to query the
MSDB of a server and write to whatever destination will be your central
repository. NOTE: If you will also be querying the MSDB of your your
destination repository, make sure you have TWO Conenction Managers, one for
the Source (which will change for each iteration of the loop) and one for the
destination, which will not change.
5) Select the Connection Manager used as the source. In the Properties box,
expand Expressions (under Misc). Click the elipsis (...). Under Property,
select "ServerName", then click the elipsis under Expression. Create an
expression by dragging the ServerName variable down to the Expression box. It
should look like this: "@[User::ServerName]"

When the package runs, this is what will happen. The SQL Task will read the
list of servers and store it in a table. The For Each loop will then iterate
over each record in that table, running the data flow each time. And each
time, the Server Name property of the Source Adapter in the data flow will
get a new value.

This is certainly not an easy first package to start with while learning
SSIS. Keep us posted here if you have questions or problems. It is possible
that I left out some minor instructions that you will uncover with errors
while trying to run it.

--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Moh" wrote:

Hello Moh:

Well, for a rookie you certainly jumped right in with both feet! This one
will have a steep learning curve for you so design your package in stages,
testing each to make sure it is doing what you want. Use breakpoints to
examine variables and properties while the package is running.

1) Create a Variable with Package scope, type = Object. Create another
package scoped variable called ServerName, string type, with an initial value
that is valid for one of your servers.
2) Add an Execute SQL task with sql of "SELECT ServerName from MyServers".
Set the result set to Full Result Set. On the Result Set page, add a Result
set, named "0" and assign it to your Object variable.
3) Add to the Control flow a For-Each task. Connect it down stream of the
SQL task. Set the ForEach task enumerator on the second page to be an ADO
Enumerator. Under ADO Object, select the Object variable. On the Variable
Mappings page, set up a mapping to the SeverName variable, indexed as 0.
4) Inside the loop, create a data flow. Design the data flow to query the
MSDB of a server and write to whatever destination will be your central
repository. NOTE: If you will also be querying the MSDB of your your
destination repository, make sure you have TWO Conenction Managers, one for
the Source (which will change for each iteration of the loop) and one for the
destination, which will not change.
5) Select the Connection Manager used as the source. In the Properties box,
expand Expressions (under Misc). Click the elipsis (...). Under Property,
select "ServerName", then click the elipsis under Expression. Create an
expression by dragging the ServerName variable down to the Expression box. It
should look like this: "@[User::ServerName]"

When the package runs, this is what will happen. The SQL Task will read the
list of servers and store it in a table. The For Each loop will then iterate
over each record in that table, running the data flow each time. And each
time, the Server Name property of the Source Adapter in the data flow will
get a new value.

This is certainly not an easy first package to start with while learning
SSIS. Keep us posted here if you have questions or problems. It is possible
that I left out some minor instructions that you will uncover with errors
while trying to run it.

--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Moh" wrote:

Hello Moh:

Well, for a rookie you certainly jumped right in with both feet! This one
will have a steep learning curve for you so design your package in stages,
testing each to make sure it is doing what you want. Use breakpoints to
examine variables and properties while the package is running.

1) Create a Variable with Package scope, type = Object. Create another
package scoped variable called ServerName, string type, with an initial value
that is valid for one of your servers.
2) Add an Execute SQL task with sql of "SELECT ServerName from MyServers".
Set the result set to Full Result Set. On the Result Set page, add a Result
set, named "0" and assign it to your Object variable.
3) Add to the Control flow a For-Each task. Connect it down stream of the
SQL task. Set the ForEach task enumerator on the second page to be an ADO
Enumerator. Under ADO Object, select the Object variable. On the Variable
Mappings page, set up a mapping to the SeverName variable, indexed as 0.
4) Inside the loop, create a data flow. Design the data flow to query the
MSDB of a server and write to whatever destination will be your central
repository. NOTE: If you will also be querying the MSDB of your your
destination repository, make sure you have TWO Conenction Managers, one for
the Source (which will change for each iteration of the loop) and one for the
destination, which will not change.
5) Select the Connection Manager used as the source. In the Properties box,
expand Expressions (under Misc). Click the elipsis (...). Under Property,
select "ServerName", then click the elipsis under Expression. Create an
expression by dragging the ServerName variable down to the Expression box. It
should look like this: "@[User::ServerName]"

When the package runs, this is what will happen. The SQL Task will read the
list of servers and store it in a table. The For Each loop will then iterate
over each record in that table, running the data flow each time. And each
time, the Server Name property of the Source Adapter in the data flow will
get a new value.

This is certainly not an easy first package to start with while learning
SSIS. Keep us posted here if you have questions or problems. It is possible
that I left out some minor instructions that you will uncover with errors
while trying to run it.

--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Moh" wrote:

Hello Moh:

Well, for a rookie you certainly jumped right in with both feet! This one
will have a steep learning curve for you so design your package in stages,
testing each to make sure it is doing what you want. Use breakpoints to
examine variables and properties while the package is running.

1) Create a Variable with Package scope, type = Object. Create another
package scoped variable called ServerName, string type, with an initial value
that is valid for one of your servers.
2) Add an Execute SQL task with sql of "SELECT ServerName from MyServers".
Set the result set to Full Result Set. On the Result Set page, add a Result
set, named "0" and assign it to your Object variable.
3) Add to the Control flow a For-Each task. Connect it down stream of the
SQL task. Set the ForEach task enumerator on the second page to be an ADO
Enumerator. Under ADO Object, select the Object variable. On the Variable
Mappings page, set up a mapping to the SeverName variable, indexed as 0.
4) Inside the loop, create a data flow. Design the data flow to query the
MSDB of a server and write to whatever destination will be your central
repository. NOTE: If you will also be querying the MSDB of your your
destination repository, make sure you have TWO Conenction Managers, one for
the Source (which will change for each iteration of the loop) and one for the
destination, which will not change.
5) Select the Connection Manager used as the source. In the Properties box,
expand Expressions (under Misc). Click the elipsis (...). Under Property,
select "ServerName", then click the elipsis under Expression. Create an
expression by dragging the ServerName variable down to the Expression box. It
should look like this: "@[User::ServerName]"

When the package runs, this is what will happen. The SQL Task will read the
list of servers and store it in a table. The For Each loop will then iterate
over each record in that table, running the data flow each time. And each
time, the Server Name property of the Source Adapter in the data flow will
get a new value.

This is certainly not an easy first package to start with while learning
SSIS. Keep us posted here if you have questions or problems. It is possible
that I left out some minor instructions that you will uncover with errors
while trying to run it.

--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Moh" wrote:

Hello Moh:

Well, for a rookie you certainly jumped right in with both feet! This one
will have a steep learning curve for you so design your package in stages,
testing each to make sure it is doing what you want. Use breakpoints to
examine variables and properties while the package is running.

1) Create a Variable with Package scope, type = Object. Create another
package scoped variable called ServerName, string type, with an initial value
that is valid for one of your servers.
2) Add an Execute SQL task with sql of "SELECT ServerName from MyServers".
Set the result set to Full Result Set. On the Result Set page, add a Result
set, named "0" and assign it to your Object variable.
3) Add to the Control flow a For-Each task. Connect it down stream of the
SQL task. Set the ForEach task enumerator on the second page to be an ADO
Enumerator. Under ADO Object, select the Object variable. On the Variable
Mappings page, set up a mapping to the SeverName variable, indexed as 0.
4) Inside the loop, create a data flow. Design the data flow to query the
MSDB of a server and write to whatever destination will be your central
repository. NOTE: If you will also be querying the MSDB of your your
destination repository, make sure you have TWO Conenction Managers, one for
the Source (which will change for each iteration of the loop) and one for the
destination, which will not change.
5) Select the Connection Manager used as the source. In the Properties box,
expand Expressions (under Misc). Click the elipsis (...). Under Property,
select "ServerName", then click the elipsis under Expression. Create an
expression by dragging the ServerName variable down to the Expression box. It
should look like this: "@[User::ServerName]"

When the package runs, this is what will happen. The SQL Task will read the
list of servers and store it in a table. The For Each loop will then iterate
over each record in that table, running the data flow each time. And each
time, the Server Name property of the Source Adapter in the data flow will
get a new value.

This is certainly not an easy first package to start with while learning
SSIS. Keep us posted here if you have questions or problems. It is possible
that I left out some minor instructions that you will uncover with errors
while trying to run it.

--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Moh" wrote:

Hello Moh:

Well, for a rookie you certainly jumped right in with both feet! This one
will have a steep learning curve for you so design your package in stages,
testing each to make sure it is doing what you want. Use breakpoints to
examine variables and properties while the package is running.

1) Create a Variable with Package scope, type = Object. Create another
package scoped variable called ServerName, string type, with an initial value
that is valid for one of your servers.
2) Add an Execute SQL task with sql of "SELECT ServerName from MyServers".
Set the result set to Full Result Set. On the Result Set page, add a Result
set, named "0" and assign it to your Object variable.
3) Add to the Control flow a For-Each task. Connect it down stream of the
SQL task. Set the ForEach task enumerator on the second page to be an ADO
Enumerator. Under ADO Object, select the Object variable. On the Variable
Mappings page, set up a mapping to the SeverName variable, indexed as 0.
4) Inside the loop, create a data flow. Design the data flow to query the
MSDB of a server and write to whatever destination will be your central
repository. NOTE: If you will also be querying the MSDB of your your
destination repository, make sure you have TWO Conenction Managers, one for
the Source (which will change for each iteration of the loop) and one for the
destination, which will not change.
5) Select the Connection Manager used as the source. In the Properties box,
expand Expressions (under Misc). Click the elipsis (...). Under Property,
select "ServerName", then click the elipsis under Expression. Create an
expression by dragging the ServerName variable down to the Expression box. It
should look like this: "@[User::ServerName]"

When the package runs, this is what will happen. The SQL Task will read the
list of servers and store it in a table. The For Each loop will then iterate
over each record in that table, running the data flow each time. And each
time, the Server Name property of the Source Adapter in the data flow will
get a new value.

This is certainly not an easy first package to start with while learning
SSIS. Keep us posted here if you have questions or problems. It is possible
that I left out some minor instructions that you will uncover with errors
while trying to run it.

--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Moh" wrote:

Hello Moh:

Well, for a rookie you certainly jumped right in with both feet! This one
will have a steep learning curve for you so design your package in stages,
testing each to make sure it is doing what you want. Use breakpoints to
examine variables and properties while the package is running.

1) Create a Variable with Package scope, type = Object. Create another
package scoped variable called ServerName, string type, with an initial value
that is valid for one of your servers.
2) Add an Execute SQL task with sql of "SELECT ServerName from MyServers".
Set the result set to Full Result Set. On the Result Set page, add a Result
set, named "0" and assign it to your Object variable.
3) Add to the Control flow a For-Each task. Connect it down stream of the
SQL task. Set the ForEach task enumerator on the second page to be an ADO
Enumerator. Under ADO Object, select the Object variable. On the Variable
Mappings page, set up a mapping to the SeverName variable, indexed as 0.
4) Inside the loop, create a data flow. Design the data flow to query the
MSDB of a server and write to whatever destination will be your central
repository. NOTE: If you will also be querying the MSDB of your your
destination repository, make sure you have TWO Conenction Managers, one for
the Source (which will change for each iteration of the loop) and one for the
destination, which will not change.
5) Select the Connection Manager used as the source. In the Properties box,
expand Expressions (under Misc). Click the elipsis (...). Under Property,
select "ServerName", then click the elipsis under Expression. Create an
expression by dragging the ServerName variable down to the Expression box. It
should look like this: "@[User::ServerName]"

When the package runs, this is what will happen. The SQL Task will read the
list of servers and store it in a table. The For Each loop will then iterate
over each record in that table, running the data flow each time. And each
time, the Server Name property of the Source Adapter in the data flow will
get a new value.

This is certainly not an easy first package to start with while learning
SSIS. Keep us posted here if you have questions or problems. It is possible
that I left out some minor instructions that you will uncover with errors
while trying to run it.

--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Moh" wrote:

Hello Moh:

Well, for a rookie you certainly jumped right in with both feet! This one
will have a steep learning curve for you so design your package in stages,
testing each to make sure it is doing what you want. Use breakpoints to
examine variables and properties while the package is running.

1) Create a Variable with Package scope, type = Object. Create another
package scoped variable called ServerName, string type, with an initial value
that is valid for one of your servers.
2) Add an Execute SQL task with sql of "SELECT ServerName from MyServers".
Set the result set to Full Result Set. On the Result Set page, add a Result
set, named "0" and assign it to your Object variable.
3) Add to the Control flow a For-Each task. Connect it down stream of the
SQL task. Set the ForEach task enumerator on the second page to be an ADO
Enumerator. Under ADO Object, select the Object variable. On the Variable
Mappings page, set up a mapping to the SeverName variable, indexed as 0.
4) Inside the loop, create a data flow. Design the data flow to query the
MSDB of a server and write to whatever destination will be your central
repository. NOTE: If you will also be querying the MSDB of your your
destination repository, make sure you have TWO Conenction Managers, one for
the Source (which will change for each iteration of the loop) and one for the
destination, which will not change.
5) Select the Connection Manager used as the source. In the Properties box,
expand Expressions (under Misc). Click the elipsis (...). Under Property,
select "ServerName", then click the elipsis under Expression. Create an
expression by dragging the ServerName variable down to the Expression box. It
should look like this: "@[User::ServerName]"

When the package runs, this is what will happen. The SQL Task will read the
list of servers and store it in a table. The For Each loop will then iterate
over each record in that table, running the data flow each time. And each
time, the Server Name property of the Source Adapter in the data flow will
get a new value.

This is certainly not an easy first package to start with while learning
SSIS. Keep us posted here if you have questions or problems. It is possible
that I left out some minor instructions that you will uncover with errors
while trying to run it.

--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Moh" wrote:

Thx very much. As you said it is jumped right in with both feet. I will try
to break the development in pieces. In case I need any help I will bug you.
thx again
Moh


"Todd C" wrote: