Export Data from SQL Server to Splunk through SSIS

SQL Server databases are commonly used to store enterprise records. It is often necessary to move this data to other locations. The CData SSIS Task for Splunk allows you to easily transfer Splunk data. In this article you will export data from SQL Server to Splunk.

Add Source and Destination Components

To get started, add a new ADO.NET Source control and a new Splunk Destination control to the data flow task.

Configure the ADO.NET Source

Follow the steps below to specify properties required to connect to the SQL Server instance.

1. Open the ADO.NET Source and add a new connection. Enter your server and database information here.
2. In the Data access mode menu, select "Table or view" and select the table or view to export into Splunk.
3. Close the ADO NET Source wizard and connect it to the destination component.

Create a New Connection Manager for Splunk

Follow the steps below to set required connection properties in the Connection Manager.

1. Create a new connection manager: In the Connection Manager window, right-click and then click New Connection. The Add SSIS Connection Manager dialog is displayed.
2. Select CData Splunk Connection Manager in the menu.
3. Configure the connection properties.

   To authenticate requests, set the User, Password, and URL properties to valid Splunk credentials. The port on which the requests are made to Splunk is port 8089.

   The data provider uses plain-text authentication by default, since the data provider attempts to negotiate TLS/SSL with the server.

   If you need to manually configure TLS/SSL, see Getting Started -> Advanced Settings in the data provider help documentation.

Configure the Splunk Destination

In the destination component Connection Manager, define mappings from the SQL Server source table into the Splunk destination table and the action you want to perform on the Splunk data. In this article, you will insert DataModels entities to Splunk.

1. Double-click the Splunk destination to open the destination component editor.
2. In the Connection Managers tab, select the connection manager previously created.
3. In the Use a Table, menu, select DataModels. In the Action menu, select Insert.
4. On the Column Mappings tab, configure the mappings from the input columns to the destination columns.

Run the Project

You can now run the project. After the SSIS Task has finished executing, data from your SQL table will be exported to the chosen table.