Introduction:
Tableau Prep and SQL Server Integration Services (SSIS) are two Extract Transform and Load (ETL) based tools. Tableau Prep came into existence in early 2018 whereas SSIS was used by users from 2005. Both these tools are in an upward curve growth in the long run. SSIS has been upgraded to a much efficient tool by Microsoft and is a first-hand choice for ETL in multiple organizations. However, Tableau Prep is developing as a new tool for ETL.
Let’s dive in to detailed differences between these two tools.
Difference between Tableau prep and SQL Server Integration Services (SSIS):
Tableau Prep:
1. Interface is user friendly. The first thing is that when you open up Tableau Prep the interface can be used by a layman who has basic SQL knowledge. Here is an example of a complex flow yet it can be easy to create. Taking different datasets from different connections and combining them just so easily is a great way for beginners to use the tool.
2. The output is stored either in csv, tableau extract(hyper) or excel(xlsx). This is a topic which has to be addressed as an important factor. This is at a developing stage and we might get to see some new output methods in near future.
3. Doesn’t allow SQL code to input data files for prep. Here we can add python scripts instead. Supported in Tableau Prep Builder version 2019.2. 1 and later and when authoring flows on the web starting in Tableau Server and Tableau Online version 2020.4. … 2 and on the web starting in version 2020.4. 1, you can use Initial SQL to query your connection.
4. Transformations are limited in Tableau Prep. This plays a major role here. The transformations are very limited.
5. Creation of calculation field values is easy.
6. Cost is high. Tableau Prep comes along-with Tableau. The three versions to purchase are:
7. Tableau Prep can be easily connected to Tableau for visualization. Visualization in Tableau may or may not be result set of ETL from Tableau Prep.
SQL Server Integration Services (SSIS):
1. Interface is not that user friendly. When you open up SSIS using Visual Studio you have to select the environment (i.e., Integration Services Project) you have to work with, then create a package (1st one is already created by default).
2. The output can be stored in databases, csv’s, xlsx etc. unlike Tableau Prep. Here the user can use their choice of output destination. Here are some ways attached in the screenshot below.
3. Allows SQL code to input data files. It can be used in making changes inside a data transformation step even. This feature can help a user significantly as he can fetch required data inside a connection manager without making changes in the original database.
4. Vast types of Transformations in SSIS. This makes it easy for users in performing any task.
5. Creation of calculation fields is harder. Here we use variables to access different values and create new fields. Furthermore, you can use derived column and use formulas to create complex calculation fields.
6. Costlier than Tableau Prep even if it is purchased as a Standard or Enterprise edition.
7. Doesn’t use any visualization tool. SSIS forms a result set after ETL which can be loaded into different BI tools for visualization. The result set formed here is much more polished than in Tableau Prep.
Conclusion:
Both the tools are effective and highly efficient in their task. Tableau Prep is a great ETL tool for beginners. This tool has a user-friendly base and thus can be easy for a layman to understand ETL processing. SSIS is a tool which banks on a skilled user for ETL. From a user’s point of view, it is the skills honed or/and the task needed, that forms the basis on which tool to use. So, what’s your choice Tableau Prep or SQL Server Integration Services (SSIS)?
