How to Create Mssql Pipeline Using Streamsets
In this tutorial we are going to see How to Create Mssql Pipeline Using Streamsets
Prerequisites
- Open firewall port 1433 from streamsets server to MSSQL Server
- Download JDBC driver Microsoft JDBC Download URL
Configure JDBC drivers for streamsets
SSH to your streamsets server
- sqljdbc41.jar requires a JRE of 7 and supports the JDBC 4.1 API
- sqljdbc42.jar requires a JRE of 8 and supports the JDBC 4.2 API
copy the sql jdbc driver to JDBC library path. In our case below is the path
/opt/streamsets-datacollector/streamsets-libs/streamsets-datacollector-jdbc-lib/lib
-rwxrwxrwx. 1 sdc sdc 660079 Feb 16 11:03 sqljdbc42.jar
-rwxrwxrwx. 1 sdc sdc 1166240 Jul 31 22:53 mssql-jdbc-7.0.0.jre8.jar
# chown sdc:sdc sqljdbc42.jar
# chown sdc:sdc mssql-jdbc-7.0.0.jre8.jar
Export the library path where .jar copied using shell scripts
vi /opt/streamsets-datacollector/libexec/sdcd-env.sh
## For SQL Database Connection
export STREAMSETS_LIBRARIES_EXTRA_DIR=/opt/streamsets-datacollector/streamsets-libs/streamsets-datacollector-jdbc-lib/lib/
Run the script
sh /opt/streamsets-datacollector/libexec/sdcd-env.sh
Restart the Streamsets service
systemctl restart sdc
Login to streamsets:http://Streamsets-IP-Address:18630
Click on Create New Pipeline and Save the Information

Now Select source as JDBC Consumer Query and Destination as Trash in streamsets created pipeline

Click on JDBC Consumer Query then see below Tabs fill required inputs

Click on JDBC

Write sql query to retrieve the data from mssql testdb

Click on advanced tab

Now validate the JDBC Connection to sqlserver it will be good and ready to fetch data