Monitoring SQL Server with SSIS

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 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.

This entry was posted in SSIS and tagged , . Bookmark the permalink.

28 Responses to Monitoring SQL Server with SSIS

  1. ranjith says:

    Thanks Mike, for posting the code. But I could not find the DB script to test it.

  2. Dick Butler says:

    Hi Mike,
    I noticed the dimTime table script is missing and how you populate it as well…any chance of adding that?


      • Dick Butler says:

        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.

      • Dick Butler says:

        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!

  3. ranjith says:

    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?

  4. Jason says:

    How are the other dim table such as DimLogReuseWait and DimRecoveryModel populated?

  5. Anthony says:

    Great site Mike! I look forward to referencing your site on upcoming projects!

  6. dennis says:

    One of the connection managers in the SSIS pkg uses database BIxPress .
    That database does not get created. How is the BIxpress db created??

  7. dennis says:

    I need to get another copy of the SSIS pkg for SQLMonitoring. Where can I find it?

  8. dennis says:

    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 ??

  9. MikeDavisSQL says:

    Dennis, You need to point the connections to your servers, you can also delete the bixpress connection.

  10. dennis says:

    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??

  11. dennis says:

    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??

    • MikeDavisSQL says:

      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.

  12. dennis says:


    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.

  13. MikeDavisSQL says:

    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.

  14. Maxime says:

    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?

  15. ragingshui says:

    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?

  16. ragingshui says:

    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.

  17. i’m unable to download the file, getting page not found error. Is this available someplace?

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s