Tableau Server: Schedule Published Extracts Using Tabcmd in SSIS
How do you maintain published extracts on your Tableau server?
There are number of ways to refresh extracts either from Tableau Desktop or directly on Tableau Server. On Tableau Server, you can schedule an automated refresh, if your data from the original data source is updated on daily basis.
But what if your Tableau extract fails or is dependent on other business processes? For example, say you have an ETL process to update your data warehouse every night and it finishes between 2:00 AM and 3:00 AM. Nobody wants to standby until it finishes just to press the refresh button from Tableau. To get around this, you could always create a task and associate it with a schedule in Tableau to make your life easier.
But what if your ETL process fails or it takes longer than what you estimated? An ETL process can be fail for many reasons: server is unexpectedly restarted, windows update, bad data, error, etc.
I had a situation where the ETL process failed but the scheduled task for refreshing extracts ran as scheduled. Guess what happened? No data in Tableau. It was not funny at all. I had to find the issue, fix it, re-run the ETL, and refresh extracts. Nobody could use Tableau while I did this.
Long story short, there is another way to avoid this kind of situation by using the Tabcmd command line utility that comes with Tableau Server. You can find the Tabcmd command line utility on your Tableau server.
By using the Tabcmd SQL SSIS Package, you get the following benefits:
- Can be event/process driven
- Users can be alerted when the extracts fail
It is very easy to use and powerful!
Here Are The Steps Needed to Refresh Extracts From the SSIS Package
Please note that you must have the Tabcmd command line utility installed on the machine in which your SSIS package runs. Very important.
- Open Notepad
- Copy and paste the command line below and replace <Your … Here>
tabcmd login -s <Your Tableau Website Here> -u <Your login ID here> -p <Your Password Here> –site “<Your Site Name Here>”
tabcmd refreshextract –site “<Your Site Name Here>” –project “<Your Project Name Here>” –datasource “<Your Extract Name Here>”
- Save it as a batch file (.bat). Make sure you change “Save as type” to All Files
- Add Execute Process Task at the end of your ETL process
- Click Edit
- Select the batch file you just created from step 3 in Executable field
- Select the Command Line Utility folder in WorkingDirectory field.
- Click OK to save.
That’s it! You are refreshing data extracts now!
Get the full report on the top BI Vendors.