Microsoft SQL Server Piece

A custom Activepieces piece for Microsoft SQL Server database integration, providing (almost) real-time monitoring and automation capabilities.

Overview

The Microsoft SQL Server piece enables you to connect to SQL Server databases and monitor table changes in real-time. It's designed to trigger workflows when new records are added to specified tables, making it perfect for data synchronization, notifications, and automated workflows.

Features

• Real-time Monitoring: Poll SQL Server tables for new records based on timestamp columns
• Secure Authentication: Support for SQL Server authentication with encrypted connections
• Flexible Configuration: Customizable table and column selection for monitoring
• Connection Validation: Built-in connection testing to ensure proper configuration
• Timeout Handling: Robust error handling with configurable timeouts

Installation

This piece is part of the custom pieces collection and should be automatically available in your Activepieces instance.

Authentication

To use this piece, you'll need to provide the following connection details:

Required Fields

• Server: The SQL Server hostname or IP address
• Database: The name of the database to connect to
• Username: SQL Server username for authentication
• Password: SQL Server password for authentication

Optional Fields

• Port: SQL Server port (default: 1433)
• Encrypt Connection: Enable encrypted connection (default: true)

Example Configuration

{
  "server": "your-sql-server.company.com",
  "database": "your_database",
  "username": "your_username",
  "password": "your_password",
  "port": 1433,
  "encrypt": true
}

Triggers

Record Added

Triggers when a new record is added to a specified SQL Server table.

Configuration

• Table Name: The name of the table to monitor (e.g., users, orders, products)
• Timestamp Column: The column that contains the timestamp for new records (e.g., created_at, updated_at, inserted_date)

How It Works

1. The piece polls the specified table at regular intervals
2. It looks for records where the timestamp column is newer than the last check
3. When new records are found, it triggers the workflow
4. The polling tracker is automatically maintained in an activepieces schema

Sample Data

{
  "id": 1,
  "name": "Sample Record",
  "created_at": "2023-01-01T00:00:00Z"
}

Database Requirements

Polling Tracker Table

The piece automatically creates and maintains a polling tracker table to keep track of the last check time. This table is created in the activepieces schema:

CREATE SCHEMA IF NOT EXISTS activepieces;

CREATE TABLE activepieces.polling_tracker (
    table_name NVARCHAR(255) PRIMARY KEY,
    last_ran_at DATETIME NOT NULL
);

Table Requirements

• The monitored table must have a timestamp column (datetime, datetime2, or similar)
• The timestamp column should be indexed for optimal performance
• The table should have appropriate permissions for the configured user

Usage Examples

E-commerce Order Monitoring

Monitor new orders in an e-commerce system:

trigger:
  type: record-added
  tableName: orders
  timestampColumn: created_at

User Registration Notifications

Track new user registrations:

trigger:
  type: record-added
  tableName: users
  timestampColumn: registered_at

Inventory Updates

Monitor product inventory changes:

trigger:
  type: record-added
  tableName: inventory_log
  timestampColumn: updated_at

Error Handling

The piece includes comprehensive error handling:

• Connection Timeouts: 15-second timeout for database connections
• Query Timeouts: 10-second timeout for connection testing
• Automatic Cleanup: Connection pools are properly closed on errors
• Detailed Logging: Console logs for debugging and monitoring

Security Considerations

• Always use encrypted connections in production environments
• Ensure the database user has minimal required permissions
• Consider using Windows Authentication for enhanced security
• Regularly rotate database passwords
• Monitor connection logs for suspicious activity

Troubleshooting

Common Issues

1. Connection Timeout

   • Check network connectivity to SQL Server
   • Verify firewall settings
   • Ensure SQL Server is accepting connections on the specified port

2. Authentication Failed

   • Verify username and password
   • Check if the user has access to the specified database
   • Ensure SQL Server authentication is enabled

3. Table Not Found

   • Verify the table name is correct
   • Check if the user has SELECT permissions on the table
   • Ensure the table exists in the specified database

4. Column Not Found

   • Verify the timestamp column name is correct
   • Check if the column exists in the specified table
   • Ensure the column contains datetime data

Debug Mode

Enable debug logging by checking the console output for messages prefixed with [MSSQL].

Development

Building the Piece

# Install dependencies
npm ci

# Build the piece
nx run pieces-mssql:build

# Run tests
nx run pieces-mssql:test

Dependencies

• mssql: ^10.0.1 - SQL Server client library
• dayjs: ^1.11.10 - Date manipulation
• @types/mssql: ^9.1.8 - TypeScript definitions

License

This piece is part of the Activepieces project and follows the same licensing terms.

Support

For issues and questions:

• Check the troubleshooting section above
• Review the console logs for error details
• Ensure your SQL Server configuration meets the requirements
• Verify network connectivity and permissions

Changelog

Version 0.0.1

• Initial release
• Basic SQL Server connection support
• Record added trigger implementation
• Connection validation and error handling