We currently have an ASP.net application exporting to Excel files in two formats. One Database per excel files: each file has M worksheet matching M SQL queries (worksheet name is SQL name). The other is One SQL query per excel file : each file has N worksheet matching N databases (worksheet name is database name). N Databases are mixed from MSSQL to Oracle to HANA. How to achieve the similar results via TDP’s export wizard and automation ?
hi jun.he:
One database per excel files - i assume you mean export all its tables into new sheet?
- Connect to desired connection
- start export wizard
- select desired database
- check desired tables that needs to be exported
- specify excel output and file path and any other settings
- at the end of the wizard check the “Save template”
- create Automation script and add “Export wizard” activity from toolbox, browse to the saved export wizard template
One SQL query per excel file - What is the contents of one sheet? Because you wrote “worksheet name is database name” but that makes no sense to me because the database can have many tables so how can you fit multiple different tables into 1 sheet? Not sure what would you like to achieve here
.
You can also enter in select queries if you want to customize the results sets. It will export one result set per worksheet. Either method of using the Export Wizard can be using in the export Automation activity
I think select to file is your best bet. In the advanced area you can tell each sql query, in each select to file, what worksheet to update (or clear and replace). Just remember to not overwrite the file when the next select to file runs (uncheck the overwrite box). Each select to file would use the same spreadsheet name with a different worksheet name.
You would name each worksheet based on the SQL you are running. Same thing for the database one where each sql running on each database you would name the worksheet based on the database the sql is running against.