SQL Server can be monitoring with SSIS packages using the DMVs in SQL Server. I did a webinar on this and you can watch the video at PragmaticWorks.com under the webinars page.
You can download the code for this webinar here.
I have updated the script. I removed the BIxPress compoents and included the date script. I have tested and it runs great.
MikeDavisSQL 
Thanks Mike, for posting the code. But I could not find the DB script to test it.
I updated the zip file to include the database scripts now.
Hi Mike,
I noticed the dimTime table script is missing and how you populate it as well…any chance of adding that?
Thanks,
dick
You can download my dim date and dim time scripts here . http://bidn.com/blogs/MikeDavis/ssis/1519/create-date-dimension-with-fiscal-and-time
Thanks Mike that worked great and I really appreciate your execellent training videos.
I have one other problem with network servers…[Get Version [40]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager “CurrentServer” failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
This is a SQL Server on the network that it can’t connect to…local server works fine. Any clues as to why this is happening.
Thanks in advance.
Mike I found the problem with the current database connection it was pointed to the DW instead of the master database. Also in the Fact load package the connection on the update fact table had to be changed from the current db to the DW database.
Thanks for a great tutorial!
Thanks a lot Mike, it worked great and I really appreciate your excellent training session & materials. You guys (at Pragmatic Works) are dong an awesome job!
BTW, do you have any SSRS report or excel template to analyze this performance data?
How are the other dim table such as DimLogReuseWait and DimRecoveryModel populated?
I just manually created them.
Great site Mike! I look forward to referencing your site on upcoming projects!
One of the connection managers in the SSIS pkg uses database BIxPress .
That database does not get created. How is the BIxpress db created??
You can delete the BIxpress connection. It is an auditing option added by BIxPress from PragmaticWorks.com
I need to get another copy of the SSIS pkg for SQLMonitoring. Where can I find it?
Ok, I was able to download the original pkg. I cannot get it to run! I get connection mgr errors. What changes do I need to make in order to get these pkgs to run ??
Dennis, You need to point the connections to your servers, you can also delete the bixpress connection.
I am trying to run this on 2008 R2, is this an issue?? I am having problems with the FactSQL pkg. There is a date type mismatch in the Get Monitor Date SK module. I cannot get around it….suggestions??
Stil lhaving an issue with the FactSQL pklg. There seems to be a datatype mismatch.We are running 2008 R2 here, is the project/pkg compatible??
The lookup is getting the surrogate key for the fact table. The lookup uses the date to reference the DimDate table and pulls back the SK. The join in the lookup is date to date. The destination puts the SK on the fact table. I updated the code and tested the packages.
Mike,
Still have not heard back from you on the FactSQL package for the SSIS monitoring project. It is still not working, there is a type mismatch for the Get Monitor Date container. I am note sure what is going on here. I have tried reloading your code off the website repeatedly to no avail. Do you have any idea as to what the problem may be? This is the only issue I have with this pkg. Everything else seems to be running correctly. I cannot get the cube to work without the FactSQL table being populated.
Dennis,
I am not sure why you are getting errors. But if it is a data conversion issue then you might need a data coversion task.
Hi Mike,
thank you for this great webinar. Unfortunately I’m having the same problem as Dennis (previous comment). In the FactSQL.dtsx for the lookup task Get Monitor Date SK the following error is raised “input column “SQLMonitorDate” and reference column named “Date” have incompatible data types…” Furthermore, the Advanced Editor the Input Column SQLMonitorDate has a DT_DBDATE data type and the output column MonitorDateSK has a DT_I4 data type. I’m new to the lookup task concept and I don’t know how to apply the data conversion task to it. Can you please help?
I have updated the script now. I removed the BixPress components and I have included the date script. I tested it and it works great.
Hi Mike,
I viewed the webinar and thought I would have a go. However, after resolving the same problems Dennis and Maxime experienced, I now have foreign key constraint “FK_FactSQL_DimDate” Error because of DateSK PK conflicts.
I changed the SQLMonitorDate to DT_I4 in the Add Server and Convert Types, as I could not work out how to do a conversion around the Look up called Get Monitor. I could no longer join the Date field from the same Look up to SQLMonitirDate and opted for the DateSK field as it seems to mirror the data in the Date field and is the the same data type.
On my system 26 rows are generated but it all fails on the insert Fact SQL Dest Data Flow.
Any Clues?
I am having the same issue with the Fact FQL Dest import routine. Since your Sept 2012 update has the link to the files been updated? I changed the SQLMonitorDate to a DT_I4 and that got me past the Get Monitor Date SK Lookup. However, it fails on the Fact SQL Dest import routine with a “FK_FactSQL_DimDate” Foreign Key constraint conflict.
SSIS isn’t my strong point so any hints would be welcomed.
Well… interesting. If I untick check constraints tick box, it loads the 26 rows of data into the Facts table.
Sorry for the delay. I have fixed the issue. I changed the data type of date on the date dimension table to date instead of date time.
i’m unable to download the file, getting page not found error. Is this available someplace?
I can email you the files. Email me at Mdavis@pragmaticworks.com