Automating Data Export

Below are detailed steps on how to use FluidShell to export from a database automatically every day. The steps automate executing a query and exporting data so that it can be performed with an OS Scheduler such as the Windows Task Scheduler.

Contents

Launch the FluidShell on a Connection

Right click on a register server and select > FluidShell (second to last entry in pop-up menu)

You should now have a fluidshell prompt similar to below

 

Set SQL Mode

FluidShell offers different modes which control how FluidShell interprets the commands you enter. Set the mode to "sql":

\cli sql

 

Connect to your Database

Now, connect to the DB that you want to execute your sql against

\change database northwind1

 

Enter Your SQL

Enter your SQL. You'll notice that the SQL hasn't actually been executed. It's been placed inside a SQL Buffer.
(To execute the SQL, you would want to type GO on a separate line, but that's not what we're going to do in this example.)

DECLARE @today datetime 
set @today = DateAdd(dd, dateDiff(dd, 0, GetDate()),0)
select * from dbo.Orders where ShippedDate < @today

 

Run the \sqlexport Command on the SQL Buffer Contents

Now we can run the sqlexport command. This command has different options -- we'll use the one that executes the contents of the SQL Buffer and then places the result set in a csv structured file.

\sqlexport -f csv -o /Users/s/Desktop/output.csv

 

Verify the CSV is Created Correctly and Save Your Commands

After you've done the above steps and validated that the csv file was created with the appropriate content, create a text file which contains all of these FluidShell commands -- see the attached sqlexport.fs file. Below is what the contents of the sqlexport.fs file look like.

\cli sql
\change database northwind1
DECLARE @today datetime 
set @today = DateAdd(dd, dateDiff(dd, 0, GetDate()),0)
select * from dbo.Orders where ShippedDate < @today
\sqlexport -f csv -o /Users/s/Desktop/output.csv

 

Execute runfluidscript-bundled.bat from the OS command line

Now, you're ready to execute this FluidShell script from the OS command line. Open a command prompt and switch to your [ADS_HOME] directory (where Aqua Data Studio is installed). Launch runfluidscript-bundled.bat, specifying your registered server name and the path of the script file to execute.

runfluidscript-bundled.bat "-cSQLServer" "-fC:\Users\s\Desktop\sqlexport.fs"

 

Schedule the runfluidscript-bundled.bat Execution

You should see your csv file created in the directory specified in the sqlexport.fs file.

You can now use the OS scheduler to automate running of this csv file on a daily basis. To launch the Windows Task Scheduler choose Start > Control Panel > Administrative Tools > Task Scheduler. When it opens, expand the Task Scheduler Library in the pane on the left and right click to Create Task and give it a meaningful name and description.

If you do not plan on being logged in when the script should run, check your Security options and make sure that you choose to allow the script to run whether you are logged out or not and with the highest privileges as shown below.

In the Triggers tab set your trigger for once a day at your desired time.

In the Actions tab of Windows Task Scheduler choose Action: Start a Program, browse to select runfluidscript-bundled.bat from within the Aqua Data Studio installation location.
Use the -cSQLServer -fC:\Users\s\Desktop\sqlexport.fs as arguments in the Actions details.
The -cSQLServer indicates the registered server in your connections and the -fC:\Users\s\Desktop\sqlexport.fs indicates the path to the FluidScript that contains all of your commands.

For "Start in", indicate the full directory path where Aqua Data Studio is installed and do not surround it with quotes.

Make sure you set the task to run once a day.

FluidShell also contains a sendmail command. Type \man sendmail to see the details of how this command works.