none
How to collect data from different queries for reporting RRS feed

  • Question

  • Hi!

    I have a rather designing problem I hope anyone can help me.

    I have a C# programm that creates powerpoints for me in which I have different statistics of the company I gonna send the report to.

    The task now is to collect the necessary statistics from the SQL Server. They are calculated based on values of different tables (like average working hours, employee numbers, number of departments asf.). The workflow would be that a project manager opens the c# tool gives the company code and can create the powerpoint document by clicking a button.

    I want to create a new table in my sql standard server 2016 where all the statistics of the company are stored since mybe I want to check them later or whatever. But I do not how to approach the problem since I have no experince and I do not know all the possibilities the SQL-Server can provide to accomplish this.

    My idea is to create a stored procedure for each statistic (that would be around 40 sp's). I will trigger the procedures from c#, where I pass the company code over to the sp's) and store the outcome in the table together with a timestamp.

    But mybe a SSRS or SSIS procedure would be better or there is something else since having 40 sp's for the process ?? Is this normal or is this like "do not do this"?

    I would really appreciate any help.

    Br!

    Tuesday, December 3, 2019 11:55 AM

Answers

All replies

  • It looks like BI project, yes SSIS package may be your best candidate here. I have no idea why do you want to create a procedure per statistics but  perhaps you need a data flow/s that gather and calculate statistics from the tables  (are they in the same db?) and insert into the "Fact" table as  you describe. 

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by ruedi006 Tuesday, December 3, 2019 1:23 PM
    Tuesday, December 3, 2019 12:02 PM
    Moderator
  • Thanks for your answer!

    I have no idea why do you want to create a procedure per statistics

    I have build up a singe source of truth and combine suvey data with salesforce data and others in the database. So writing one query that provides all the statistics would be (if possible) very long and confusing.

    E.g. I want to calculate the statisfaction of employees (survey data) and Return Rates based based on Invitation by Region (Salesforce).

    I guess I can trigger the SSIS from C# and can provide an input paramter (Company ID)?

    And I guess I will be able to set up multiple queries for the statistics separatley as well? Then I will have a deeper look into SSIS.

    Tuesday, December 3, 2019 12:28 PM
  • >>>I guess I can trigger the SSIS from C# and can provide an input paramter (Company ID)?

    If statistics do not change frequently you can build the package and schedule it  at night by SQL Agent job.

    >>>And I guess I will be able to set up multiple queries for the statistics separatley as well? Then I will >>>have a deeper look into SSIS.

    Yep you can even have a one data flow per statistic and it will be easy to debug on per statistics level.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, December 3, 2019 12:34 PM
    Moderator
  • >>>I guess I can trigger the SSIS from C# and can provide an input paramter (Company ID)?

    If statistics do not change frequently you can build the package and schedule it  at night by SQL Agent job.

    No fortunately just occasionally. I will have to trigger this by the project managers every time they need a report (-> open the c# tool and create the report). But I guess I can trigger the SSIS Package with C# providing a parameter for the package?

    Tuesday, December 3, 2019 12:38 PM
  • >>But I guess I can trigger the SSIS Package with C# providing a parameter for the >>package?

    Yep sure

    https://stackoverflow.com/questions/273751/how-to-execute-an-ssis-package-from-net

    /*

    Code Snippet

    using Microsoft.SqlServer.Dts.Runtime;

    Microsoft.SqlServer.Dts.Runtime.Application integrationServices = new Application();
    Package ssisPackage = integrationServices.LoadFromSqlServer(packagePath, serverName, null, null, null);
    ssisPackage.Variables["ParameterName"].Value = "ParameterValue";
    DTSExecResult result = ssisPackage.Execute();

    */


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, December 3, 2019 12:50 PM
    Moderator
  • Wow, thank you for your help!

    I have one last question.

    The process would be:

    1. The Project Manager starts the C# Tool for Presenation creation and delivers the company information

    2. C# delivers the company ID to SSIS and triggers the SSIS Package

    3. SSIS Package creates all the statistics and writes them into a table

    4. The C# program grabs the information out of the table and creates the report

    between step 3 and 4 I need a mechanism that the c# program knows when the values are in the table and when it can start creating the reports. Is that also possible?

    Tuesday, December 3, 2019 1:37 PM
  • I think your program should give some info that the package is competed successfully 

    https://www.sqlservercentral.com/forums/topic/best-way-to-get-successfailure-notification-from-ssis-package-stages


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Tuesday, December 3, 2019 1:42 PM
    Moderator
  • I'm not sure why there would be 40 stored procedures. Is this because each statistics value has its own computation formula?

    The new table would have one row per statistics value and some code that goes with it to identify it. (That could be a numeric id, some mnemonic string.)

    Personally, I don't see the point with dragging SSIS into the mix. If you need to fire the computation, you could just as well do that from the C# program. Then again, since I don't know SSIS, maybe I don't see the point with it.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, December 3, 2019 10:31 PM
    Moderator