Database Reference
In-Depth Information
To accomplish this, you will create data flows within your Foreach Loop container
to retrieve the metadata from each server and store it in the
dbaCentralLogging
database. The Data Flow task is arguably the most frequently used task in Integration
Services. It allows you to easily move data between servers and, if necessary, perform
data conversions or cleansing.
Drag a Data Flow task from the SSIS Toolbox into the Foreach Loop container and
rename it
Database Size
. Double-clicking the Data Flow task will open the Data Flow
Designer tab. Notice that the objects available within the toolbox change once you are
inside the Data Flow designer. Drag the OLE DB Source icon onto the designer surface
and rename it
Dynamic SQL Source
. Double-click the icon to edit its properties.
Select DynamicSQLServerInstance in the OLE DB Connection Manager drop-
down. Change the Data Access Mode to SQL Command, and then copy the code from
Listing 1-5
into the SQL Command text box.
Listing 1-5
.
Example of T-SQL to Retrieve Current Data and Log File Sizes for All
Databases on the Server
SELECT GETDATE() AS [captureDate]
, @@SERVERNAME AS [serverName]
, instance_name AS [databaseName]
, SUM(
CASE
WHEN counter_name = 'Data File(s) Size (KB)'
THEN cntr_value
END
) AS 'dataSizeInKB'
, SUM(
CASE
WHEN counter_name = 'Log File(s) Size (KB)'
THEN cntr_value
END
) AS 'logSizeInKB'
FROM sys.dm_os_performance_counters
WHERE counter_name IN ('Data File(s) Size (KB)'
,'Log File(s) Size (KB)')
/* optional: remove _Total to avoid accidentally
double-counting in queries */
Search WWH ::
Custom Search