December 7, 2010 at 10:12 am
I have created a package that picks up server data from a table inside my database, that data is placed in a foreach loop which then goes out and obtains data about the server using various commands. The problem is it picks up the first row in the table and runs until it has gone through all of the records in the table. But the data that it returns if for the first row in the table.
Server1
server2
server3
Result
server1 drivec size3g
server2 drivec size3g
server3 drivec size3g
Even though I know that server2's drivec has 4 gig and server3's drivec has 10g.
Not sure why the looping is not correct. If you need more information, let me know. Thanks for any assistance.
December 7, 2010 at 10:14 am
It looks like it is running your drive space check against server 1 for each iteration of the loop. Can you explain more as to what type of task you are using inside your for each loop to get the drive sizes?
December 7, 2010 at 11:35 am
I have tried as best I can to provide everything that is in the Loop. If you need anything else, let me know.
Data Flow task –
Ole db source
Recordset destination
Foreachloop – target hosts
Collection – foreach ado enumerator
User::targethostsRS
Rows in the first table
Variable mappings:
User:hostname 0
User::loginame 1
User::loginpassword 2
User::dbservertype 3
User::servername 4
User::instancename 5
User::portnumber 6
Inside the loop:
Execute SQL task
Single row
Connect to the Server w/Database
SQLStatement:
SELECT count(*)
FROM kmn.servers
WHERE (host_name = ?) AND (server_name = ?) AND (instance_name = ?)
Parameter Mapping:
User::hostname input varchar 0 -1
User::servername input varchar 1 -1
User::instancename input varchar 2 -1
Result set:
0user::serverexists
Execute SQL Task
Single Row
Connect to the same server w/database
Direct input
SQLStatement:
insert into kmn.servers (host_name, server_name, instance_name, db_server_type) values (?,?,?,?)
SELECT CAST(@@IDENTITY AS INT) AS server_id
Parameter mappings:
User::Hostnameinput varchar 0 -1
User::Servernameinput varchar 1 -1
User::Instancenameinput varchar 2 -1
User::dbsServerTypeinput varchar 3 -1
Result Set:
0user::serverid
Execute SQL Statement
Single row
Connect to same server with database
Direct input
Sqlstatement: (so we can date stamp snapshots)
INSERT INTO [kmn].[snapshots] (snap_timestamp) VALUES (GETDATE())
SELECT CAST(@@IDENTITY AS INT) AS snapshot_id
GO
ResultSet:
0user::snapshotID
Dataflow task:
OLE DB Source:
SQL command:SET FMTONLY OFF
EXEC master..xp_fixeddrives
Columns : name, drive, mbfree
Derived Column
Snapshot_id add as new column @[user::snapshotid]
Server_id add as new column @[user::serverid]
OLE DB Destination:
Connection to server with database
Table or view
kmn.hostinfo
Mappings:
List of columns
Dataflow task:
Ole db source:
Connection to server with database
SQL cmd variable
User::sqlcommand –
Variable value:
SELECT [os_drive] FROM [kmn].[host_info] WHERE snapshot_id = 0 AND OS_DRIVE IS NOT NULL
Columns:
Os_drive
Recordset destination:
Componenet properties – variable: user::targetosdrivesRS
Input columns – os_drive os_drive
Input column – os_drive
FLC Loop on Host drive info: (loop within the first loop)
Collection:
Foreach ADO enumerator
User::targetOSDrivesRS
Rows in the first table
Variable mappings:
User::targetOSDrive 0
Execute SQL Task:
Single row
Connect to MSSQL_ReportDS
Direct Input
SQLStatement:
SET NOCOUNT ON
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20) DECLARE @MB Numeric ; SET @MB = 1048576
set @drive = ?
EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT IF @hr <> 0
EXEC sp_OAGetErrorInfo @fso
EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso EXEC @hr =
sp_OAGetProperty
@odrive,'TotalSize', @TotalSize OUT IF @hr <> 0 EXEC sp_OAGetErrorInfo
@odrive select CAST(@TotalSize/@MB as INT)
Parameter Mapping:
user::targetOSDrive input nvarchar 0 -1
Result Set:
0 user::totalsize
Execute SQL Task:
Result Set – None
Connect to server with database
Direct input
Sql statement:
UPDATE [kmn].[host_info] set os_total = ? WHERE snapshot_id = ? AND [server_id] = ? AND [os_drive] = ?
Parameter mapping:
User::totalsize inputlarg_integer 0 -1
User::snapshotidinputnumeric1 -1
User::serveridinputnumeric2 -1
User::targetosdriveinputnvarchar3 -1
Execute SQL Task:
ResultSet – none
Connect to server with database
Direct input
SQLStatement:
UPDATE [kmn].[host_info] SET [os_used] = [os_total]-[os_free] WHERE snapshot_id = ? AND [server_id] = ? AND [os_drive] = ?
Parameter mapping:
User::snapshotidinputnumeric 0 -1
User::serveridinputnumeric 1 -1
User::targetosdriveinputnvarchar 2 -1
December 7, 2010 at 12:53 pm
Thank you for the detailed layout of your package. First off, it looks like you are using a data flow task along with the RecordSet destination in order to read in your initial record set that your loop is going to work off of. But how are you getting those values in to targethostsRS?
The Recordset Destination does not work with the foreach ADO enumerator like that. I would suggest using an ExecuteSQL task to get your initial hosts list. Configure the task to use a Full result set and map the result set to your targethostsRS variable. Then, use this variable (make sure it is of type object) in your for each ADO enumerator.
Make sense?
December 7, 2010 at 12:57 pm
Thanks for the quick reply, I will give your suggestion a try and let you know.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply