question

HARDRESET3144-7628 avatar image
0 Votes"
HARDRESET3144-7628 asked Monalv-msft edited

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

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-generalsql-server-reporting-servicessql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Monalv-msft avatar image
0 Votes"
Monalv-msft answered Monalv-msft edited

Hi @HARDRESET3144-7628 ,

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.




variables.png (9.7 KiB)
oledb-source.png (32.4 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Mona,

  1. May I know if the structures of the sql tables(dbo.010190,dbo.020190,dbo.030190) are same(same columns and same datatypes)?

         **Yes, all the columns and data types are the same.**
    

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

        **I attach an screenshot. Tables are under ASSETS.

80184-2.jpg

---- Also ı have an EXISTING EXCEL FILE and ı connect excel with microsoft query that takes 36 stored queries from SQL server.
---- ı must take the queries reports into EXISTING EXCEL FILE for all the tables in ASSETS.
---- And changing ONE BY ONE all the 36 pieces of tables EXISTING DEFINED QUERY CODE in EXISTING EXCEL FILE is very hard for all the tables. ( JUST NEED TO
CHANGE "dbo.010190" ).
----ALSO ı ask how can ı do this procedur automatically for all the tables in ASSETS.
---- IF YOU TELL ME THE SOLUTION DETAILED, I WILL BE VERY HAPPY :)
THANKS FOR MILLIONS TIME FOR YOUR ANSWER.

Best regards
Osman


0 Votes 0 ·
2.jpg (132.3 KiB)
Monalv-msft avatar image
0 Votes"
Monalv-msft answered HARDRESET3144-7628 commented

Hi @HARDRESET3144-7628 ,

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





cf.png (32.0 KiB)
variables.png (10.1 KiB)
est-general.png (32.3 KiB)
est-resultset.png (12.4 KiB)
flc-collection.png (16.4 KiB)
df.png (81.1 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

HI MONA,

I STILL CAN'T REACH THIS WINDIWS. MUST I HAVE ANY EXTENSION PACK LOADED TO BE SEEN THIS WINDOWS.(EXECUTE SQL TASK EDITOR WINDOW, VARIABLE WINDOW, FOREACHLOOP EDITOR WINDOW)

I JUST ACCOMPLISH FIRST STEP;

       SELECT TABLE_NAME 
       FROM TestDB.INFORMATION_SCHEMA.TABLES; ( THIS IS DONE BUT OTHERS CANT BE DONE. BECAUSE I CANT REACH THAT WINDOWS.)

THANKS FOR YOUR HELP MONA.

0 Votes 0 ·
Monalv-msft avatar image Monalv-msft HARDRESET3144-7628 ·

Hi @HARDRESET3144-7628 ,

1.May I know if you want to use sql query to export data automatically by stored procedure to existing EXCEL file?

2.Or we can download SQL Server Data Tools (SSDT) for Visual Studio and then create Integration Services (SSIS) Projects and Solutions.

Best regards,
Mona

0 Votes 0 ·

Hi @Monalv-msft,

YES WE DO USE SQL QUERY TO EXPORT DATA automatically by stored procedure to existing EXCEL file.

I download SQL Server Data Tools (SSDT) for Visual Studio then ı find the windows. I try to do your prosedures that is shown pictures above but visual studio shows such an error message in screenshot.81870-error1.jpg

I also download and install the file that is shown in error message. but there is the same error message in the project.

ı still need your help.

thanks.


0 Votes 0 ·
error1.jpg (112.9 KiB)