locked
Perfmon Counters for SSIS Pipeline RRS feed

  • Question

  • Having issues accessing perfmon counters for SSIS Pipeline.  I cant access them locally or remotely.  

     

    Is there a hotfix to resolve this issue or is there another course of action to get all the SSIS pipeline counters available?

     

    We are running windows 2008r2 64bit and sql server 2008r2.  Perfmon does not display counters for SSIS Pipeline locally.  I need to also access these remotely to use with a monitoring application, how can I make them available remotely and locally?


    Chad SWC | TECHNOLOGY PARTNERS 1420 Kensington Road, Suite 110 Oak Brook, Illinois 60523-2144
    Thursday, February 10, 2011 3:56 PM

Answers

  • The issue is now resolved on all servers.  We believe that using the suggestions from this threaded discussion in concert with reloading the counter has resolved the issue for us.

    lodctr c:\"program files"\"microsoft sql server"\100\dts\binn\perf-DTSPipeline100DTSPERF.INI


    Chad Dotzenrod SWC | TECHNOLOGY PARTNERS 1420 Kensington Road, Suite 110 Oak Brook, Illinois 60523-2144 http://www.swc.com
    • Marked as answer by Chad - TSP Wednesday, March 9, 2011 1:33 PM
    Wednesday, March 9, 2011 1:33 PM

All replies

  • Did that happen after an upgrade to CU4?

    Please check this article: https://connect.microsoft.com/SQLServer/feedback/details/605740/ssis-pipleine-10-0-counters-missing?wa=wsignin1.0, if applicable. It also has a hotfix KB 561760 mentioned.


    Arthur My Blog
    By: TwitterButtons.com
    Thursday, February 10, 2011 4:03 PM
  • You may need to add permissions to the account(s)/group(s) to which you belong to allow you to view perfmon counters.  See this thread.
    Todd McDermid's Blog Talk to me now on
    Thursday, February 10, 2011 5:53 PM
  • We have had the problem on all 64 bit sql service/ssis installations and it didn’t occur after/before updates like CU4.  We are currently running sql server 2008r2 10.50.1600.  Also I am in the administrators group and still cannot see the counters so I don’t think its a permissions issue.  I cannot see the Pipeline counters even in 32 bit perfmon though (running this command to open 32 bit perfmon mmc.exe /32 perfmon.msc), so I'm not sure if its purely a 32/64 bit issue.  Just to be clear, we can see other SSIS counters, just not the Pipeline SSIS counters.  


    Chad SWC | TECHNOLOGY PARTNERS 1420 Kensington Road, Suite 110 Oak Brook, Illinois 60523-2144
    Thursday, February 10, 2011 10:00 PM
  • There was a bug on SSIS pipeline perf object and sometimes it will disappear from the list. But in SQL Server R2 CU4 we already released a hotfix (http://support.microsoft.com/kb/2261403 and http://support.microsoft.com/kb/2345451). Chad, do you mean your problem still exists even after you installed SQL Server R2 CU4?

    Friday, February 11, 2011 7:36 AM
  • We'll give that suggestion a try and report back on success/failure. 
    Chad SWC | TECHNOLOGY PARTNERS 1420 Kensington Road, Suite 110 Oak Brook, Illinois 60523-2144
    Friday, February 11, 2011 3:30 PM
  • We applied CU4 and it worked on one server but not the other.  We have an SSIS machine and a SQL Server Database Engine machine, it worked on the sql server machine (I see SQL Server: SSIS Pipeline counters there) but not on the SSIS machine.  Is there a way to use lodctr/unlodctr with these particular counters to make sure they are loaded properly?  It's possible that through some initial troubleshooting they were unloaded.


    Chad Dotzenrod SWC | TECHNOLOGY PARTNERS 1420 Kensington Road, Suite 110 Oak Brook, Illinois 60523-2144 http://www.swc.com
    Friday, February 11, 2011 9:46 PM
  • It's strange that CU doesn't work on one of machines. I guess loadctr/unlodctr wouldn't work because the bug is due to a permission issue on kernel objects, not due to registry/DLL location issues.

    What's your user account to run SSIS packages? That bug usually happens when the account to run package is different to the account to run perf monitor. Try to use same account to see whether the problem still exists. If it still exists, could you contact MS custom service so that our team can get detailed information on that SSIS machine? Thanks.

    Monday, February 14, 2011 1:28 AM
  • We are using a different account to run ssis packages, do we just need to run ssis packages once with the administrator/perfmon account to get the counters to show up or does this ssis account need to be granted the permissions on the counters.  If the user needs the permission granted can you specify how to grant the needed privileges?


    Chad Dotzenrod SWC | TECHNOLOGY PARTNERS 1420 Kensington Road, Suite 110 Oak Brook, Illinois 60523-2144 http://www.swc.com
    Monday, February 14, 2011 5:07 PM
  • I think there are 2 things you could have a try.

    1. Add the user account that run the package into "Performance Log Users" or "Performance Monitor Users" group

    2. Grant the permission of "create global object" to that acoount (Administrative Tools\Local Security Policy\User Right Assignment\Create global object) 

    Wednesday, February 16, 2011 3:16 AM
  • Added package executor to the performance log users and performance monitor users group, granted permission to create global object.  Successfully executed an SSIS package as that user, perfmon counters still not showing up when checking as that user or as administrator.  After applying CU4 the sql server version is 10.50.1746.

    One thing to note is on the server where the counters are working, the service account that runs Sql Server Integration Service is the NETWORK SERVICE account.  On the SSIS server however, we have a separate domain account [mssqlisdev] that runs the service (and what is installed under I believe).  This is not the account that is actually running the jobs however.  In addition to granting the package executor account the privileges suggested, We also granted those privileges to the [mssqlisdev] account and restarted the service and server, still no perfmon counters.

     


    Chad Dotzenrod SWC | TECHNOLOGY PARTNERS 1420 Kensington Road, Suite 110 Oak Brook, Illinois 60523-2144 http://www.swc.com
    Thursday, February 17, 2011 2:03 PM
  • Did you try to access the perf counter when the package was still running?

    If you just open the perf monitor with different account, is the pipeline perf counter shown there?

    Friday, February 18, 2011 3:07 AM
  • Can't access the pipeline counters with another account and can't access them when a package is running.
    Chad Dotzenrod SWC | TECHNOLOGY PARTNERS 1420 Kensington Road, Suite 110 Oak Brook, Illinois 60523-2144 http://www.swc.com
    Friday, February 18, 2011 6:55 PM
  • We're at a standstill on this issue.  Any other suggestions?  How do I start a case with Microsoft?  I believe all options and suggestions have been exhausted from my clients perspective. 
    Chad Dotzenrod SWC | TECHNOLOGY PARTNERS 1420 Kensington Road, Suite 110 Oak Brook, Illinois 60523-2144 http://www.swc.com
    Thursday, March 3, 2011 1:38 PM
  • http://support.microsoft.com/?LN=en-us&scid=fh%3Ben-us%3Bofferprophone&x=13&y=12 page contains options to submitting a technical support case.

    Arthur My Blog
    By: TwitterButtons.com
    Thursday, March 3, 2011 3:27 PM
  • The issue is now resolved on all servers.  We believe that using the suggestions from this threaded discussion in concert with reloading the counter has resolved the issue for us.

    lodctr c:\"program files"\"microsoft sql server"\100\dts\binn\perf-DTSPipeline100DTSPERF.INI


    Chad Dotzenrod SWC | TECHNOLOGY PARTNERS 1420 Kensington Road, Suite 110 Oak Brook, Illinois 60523-2144 http://www.swc.com
    • Marked as answer by Chad - TSP Wednesday, March 9, 2011 1:33 PM
    Wednesday, March 9, 2011 1:33 PM