Thanks to all who came to today's session on Introduction to SSIS. Tomorrow's session is on SSIS for the DBA. If you have not registered, I'm afraid we've maxed out the conference system at this point with 3200 people spread across Wednesday's sessions and we're expecting the typical webinar drop off, which should still be a large number. Hopefully on Wednesday we don't "stress test" the Gotowebinar system like today, bringing it to its knees :).

The Wednesday session has a number of examples for the DBA and how you can use SSIS. For example, one example is a package (see below download) that will loop through a list of database instances and capture the worst performing queries. I've also including some quite ugly but functional reports to show you the worse performing queries. To get this working in your environment, simply run the CreateScript.sql file in a database you want to log all this information into. Then populate the MonitoredServers table with a list of instances that you have access to that you wish to monitor.

This sample is provided "as is" with no warranty. Please modify it however you wish and run at your own risk. One thing to keep in mind that it does flush the procedure cache after it runs for statistical purposes so don't run this that often. I'm more showing this to you as an learning example.

Download the example:

SSISfortheDBA.zip

The setup for this will all be covered in Wednesday's session (http://www.dtsxchange.com/freebisessions.asp) and we will be recording the sessions for those who can't make it. Expect the recordings by next week on this site.

Oops! In the zip file, I left out the ChangeTracking table, which can be found here in this script:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ChangeLog]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ChangeLog](
    [ChangeID] [int] IDENTITY(1,1) NOT NULL,
    [ChangeTitle] [varchar](150) NULL,
    [ServerName] [varchar](25) NULL,
    [SchemaName] [varchar](25) NULL,
    [ChangeDescription] [varchar](2000) NULL,
    [ChangeDate] [datetime] NULL,
    [ChangeUser] [varchar](50) NULL,
CONSTRAINT [PK_ChangeLog] PRIMARY KEY CLUSTERED
(
    [ChangeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF

Thanks!

Brian Knight