Export data automatically by stored procedur to existing EXCEL file from SQL Server (from Multiple database)

HARD RESET3144 1 Reputation point
2021-03-20T10:27:55.9+00:00

Hi, ı am Osman from Turkey. I am interested in MICROSOFT SQL SERVER MANAGEMENT STUDIO. I need some help about SQL SERVER. I try to automatize my reports . I have 365 databese TABLE in SQL SERVER and ı have 36 piece stored procedures in existing Excel file, Microsoft Query connected with SQL. I want that SQL Server copy my existing EXCEL file then run 36 stored procedur then put the query report in it then put the file output destination. I need an SQL CODE for this automation. I search but not find an answer. I HAVE QUERİES LİKE DOWN. SELECT * FROM dbo.010190 WHERE FLOWER='PİNK' NOW I JUST WANT TO CHANGE AUTOMATİCALLY "dbo.010190" in every query. Because other query will be executed from dbo.020190 and other 030190 and so go on. Is there a solution for this to make it automatically. THERE IS SOME CONSTRAINTS. 1. ALL DATABASE TABLES IN SQL HAVE DIFFERENT NAME. 2. EXISTING EXCEL FILE IS NEVER CHANGED AFTER QUERY AND REUSED FOR THE NEXT EXECUTION. 3. EXISTING EXCEL FILE ARE WRITTEN TO DESTINATION FOLDER AFTER NEW QUERY REPORTS. 4. AND I WANT IT BY PRESSING ONE BUTTON.��(I HAVE VISUAL STUDIO TOO ON MY PC) 5. IF I CAN’T SOLVE IT, I MUST SPARE MY TIME SO MUCH FOR THIS PROCEDURE. IF YOU HELP ME ABOUT THIS SUBJECT, I WILL BE VERY HAPPY. OUR DEAR SOFTWARE ENGINEERS HAVE ABSOLUTELY FIND A SOLUTION TO THIS ISSUE. I AM WAITING FOR YOUR ANSWER. THANKS FOR YOUR SINCERITY AND HELP. ��

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,711 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,799 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,452 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Monalv-MSFT 5,891 Reputation points
    2021-03-24T03:04:48.513+00:00

    Hi @HARD RESET3144 ,

    1.I must take the queries reports into EXISTING EXCEL FILE for all the tables in ASSETS.
    Please test the following sql query in SSMS firstly:

    SELECT TABLE_NAME   
FROM TestDB.INFORMATION_SCHEMA.TABLES;

    80830-selecttablenameindatabase.png

    2.Then please drag Execute SQL Task, Foreach Loop Container, Data Flow Task and and create the variables in SSIS packages.
    Please refer to the following pictures:
    80957-cf.png
    80937-variables.png
    80889-est-general.png
    80938-est-resultset.png
    80899-flc-collection.png
    80890-flc-variablemappings.png
    80900-df.png

    Best regards,
    Mona

    1 person found this answer helpful.
  2. Monalv-MSFT 5,891 Reputation points
    2021-03-22T08:12:31.62+00:00

    Hi @HARD RESET3144 ,

    *I HAVE QUERİES LİKE DOWN. SELECT * FROM dbo.010190 WHERE FLOWER='PİNK' NOW I JUST WANT TO CHANGE AUTOMATİCALLY "dbo.010190" in every query.
    Because other query will be executed from dbo.020190 and other 030190 and so go on.*

    May I know if the structures of the sql tables(dbo.010190,dbo.020190,dbo.030190) are same(same columns and same datatypes)?
    If so, we can store the sql table names and sql query in SSIS variables.
    Then we can choose Data access mode as SQL command from variable in OLEDB Source.
    Once we change the value in variable SqlTableName, the value in variable SqlQuery will be changed.
    80088-variables.png
    80114-expressionbuilder.png
    80115-oledb-source.png

    May I know where do you store these sql table names?

    Best regards,
    Mona

    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.