How to Create Mssql Pipeline Using Streamsets

In this tutorial we are going to see How to Create Mssql Pipeline Using Streamsets

Prerequisites

1. Open firewall port 1433 from streamsets server to MSSQL Server
2. Download JDBC driver  Microsoft JDBC Download URL

Configure JDBC drivers for streamsets

SSH to your streamsets server

1. sqljdbc41.jar requires a JRE of 7 and supports the JDBC 4.1 API
2. 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