Skip to main content

Wait Column

Waits until a new column is added to a specified table in Microsoft SQL Server.

Common Properties​

  • Name - The custom name of the node.
  • Color - The custom color of the node.
  • Delay Before (sec) - Waits in seconds before executing the node.
  • Delay After (sec) - Waits in seconds after executing node.
  • Continue On Error - Automation will continue regardless of any error. The default value is false.
info

If the ContinueOnError property is true, no error is caught when the project is executed, even if a Catch node is used.

Inputs​

  • Connection Id - The unique identifier of the database connection to use (optional if credentials provided directly).
  • Table Name - Name of the table to monitor for new columns. Can include schema (e.g., dbo.TableName).

Options​

  • Poll Time (s) - Time interval in seconds between checks for new columns (default: 1.0 second).
  • Credentials - SQL Server database credentials (optional). Use this if you want to monitor without a Connect node.

Output​

  • None - The node completes when a new column is detected in the specified table.

How It Works​

The Wait Column node monitors a table for new columns being added. When executed, the node:

  1. Retrieves the database connection (either from connection ID or creates new connection from credentials)
  2. Validates the table name is provided
  3. Queries INFORMATION_SCHEMA.COLUMNS to count columns in the table
  4. On first execution, records the initial count of columns
  5. Continuously polls the database at the specified interval
  6. When the count increases (new column added), the node completes
  7. Supports both simple table names and schema-qualified names

Requirements​

  • Either: A valid connection ID from Connect node OR valid database credentials
  • Valid table name (table must exist before monitoring starts)
  • Appropriate SELECT permission on INFORMATION_SCHEMA views
  • Connection must remain open during monitoring

Error Handling​

The node will return specific errors in the following cases:

  • ErrInvalidArg - Table name is empty
  • ErrNotFound - Connection ID not found
  • ErrConnection - Cannot connect to SQL Server (when using credentials mode)
  • ErrRuntime - Query execution failed, table doesn't exist, or connection closed during monitoring

Table Name Formats​

Simple Table Name​

Table Name: Customers

Monitors table "Customers" in the default schema.

With Schema​

Table Name: dbo.Orders

Monitors table "Orders" in schema "dbo".

Table Name: staging.ImportData

Monitors table "ImportData" in schema "staging".

Usage Examples​

Wait for Schema Change​

Connect
  ↓
Wait Column
  - Table Name: dbo.Products
  - Poll Time: 2.0
  ↓
(triggered when any column is added to Products table)
  ↓
Query (get new column information)
  ↓
Process Schema Change
  ↓
Disconnect

Monitor Staging Table​

Wait Column
  - Table Name: staging.ImportTable
  - Poll Time: 1.0
  ↓
(triggered when column added)
  ↓
Query (SELECT * FROM staging.ImportTable)
  ↓
Validate New Column

ETL Schema Evolution​

Wait Table (wait for table creation)
  ↓
Wait Column (wait for all required columns)
  ↓
Query (verify schema is complete)
  ↓
Begin Data Processing

Common Use Cases​

  1. Schema Evolution Monitoring - Detect when external systems add columns to tables
  2. ETL Pipeline Coordination - Wait for upstream processes to complete table schema
  3. Dynamic Schema Adaptation - Adapt processing logic when new columns appear
  4. Database Migration Monitoring - Track schema changes during migrations
  5. Multi-System Integration - Coordinate with systems that alter table structure
  6. Incremental Schema Deployment - Wait for schema changes to be applied
  7. Data Warehouse Updates - Monitor for new dimension or measure columns

Tips for Effective Use​

  • Table Must Exist - The table must exist before monitoring starts
  • Schema Specification - Include schema name to avoid ambiguity
  • Poll Interval - Adjust based on expected frequency of schema changes
  • Connection Stability - Use credentials mode for long-running monitors
  • Combine with Wait Table - First wait for table, then wait for columns
  • Verify Column - After trigger, query to identify which column was added
  • Timeout Logic - Implement timeout if column might never be added

Polling Behavior​

How Polling Works​

  1. Initial Check - Counts existing columns in table
  2. Wait Period - Sleeps for Poll Time seconds
  3. Subsequent Check - Counts columns again
  4. Compare - If count increased, triggers; otherwise continues polling
  5. Repeat - Steps 2-4 repeat until new column detected

Poll Time Recommendations​

Fast Polling (0.5 - 1 second):

  • Quick response to schema changes
  • More database queries
  • Good for active schema deployments

Medium Polling (2 - 5 seconds):

  • Balanced resource usage
  • Acceptable response time
  • Good for most scenarios

Slow Polling (10+ seconds):

  • Minimal resource usage
  • Delayed response
  • Good for background monitoring

INFORMATION_SCHEMA Query​

The node queries SQL Server's INFORMATION_SCHEMA:

Without Schema in Table Name​

SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE 'TableName'

With Schema in Table Name​

SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'SchemaName'
  AND TABLE_NAME LIKE 'TableName'

Detecting Which Column Was Added​

After Wait Column triggers, identify the new column:

-- Query to get most recently added column
SELECT TOP 1
  COLUMN_NAME,
  DATA_TYPE,
  CHARACTER_MAXIMUM_LENGTH,
  IS_NULLABLE,
  COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = '{{schema}}'
  AND TABLE_NAME = '{{tableName}}'
ORDER BY ORDINAL_POSITION DESC

Note: This assumes the newest column has the highest ordinal position, which is usually but not always true.

Better Approach - Compare Before and After​

// Before Wait Column - store current columns
const beforeColumns = $item.currentColumns;  // From a Query

// After Wait Column - get new columns
const afterColumns = $item.newColumns;  // From another Query

// Find the difference
const newColumns = afterColumns.filter(col =>
  !beforeColumns.some(before => before.COLUMN_NAME === col.COLUMN_NAME)
);

console.log('New columns:', newColumns);

Advanced Examples​

Wait for Specific Number of Columns​

Query (count current columns)
  ↓
JavaScript (store initial count)
  ↓
Loop (until desired column count):
  Wait Column
  ↓
  Query (count columns again)
  ↓
  JavaScript (check if count matches target)
  ↓
(exit loop when all columns added)

Monitor Multiple Tables​

// Monitor multiple tables for schema changes
const tablesToMonitor = [
  'dbo.Customers',
  'dbo.Orders',
  'dbo.Products'
];

// Use parallel flows or loop
// Each table gets its own Wait Column node

Schema Deployment Verification​

Non Query (ALTER TABLE ADD COLUMN)
  ↓
Wait Column (verify column was added)
  ↓
Query (test column is accessible)
  ↓
Non Query (create index on new column)
  ↓
Verify Deployment Complete

Dynamic ETL Adaptation​

Wait Column (wait for new data column)
  ↓
Query (get column metadata)
  ↓
JavaScript (generate dynamic ETL logic for new column)
  ↓
Non Query (update ETL configuration)
  ↓
Run Updated ETL Process

Cascade Schema Monitoring​

Wait Table (table created)
  ↓
Wait Column (first column added)
  ↓
Wait Column (second column added)
  ↓
Wait Column (third column added)
  ↓
(all required columns present)
  ↓
Begin Processing

Migration Monitoring​

Non Query (run migration script)
  ↓
Parallel:
  Wait Column (Table1)
  Wait Column (Table2)
  Wait Column (Table3)
  ↓
(all columns added)
  ↓
Verify Migration Success
  ↓
Non Query (update migration status)

Common Errors and Solutions​

Table Name Cannot Be Empty​

Error: ErrInvalidArg: Table name cannot be empty

Solutions:

  • Provide a table name in the Table Name field
  • Ensure the variable containing the table name has a value
  • Include schema if table is not in default schema

Table Does Not Exist​

Error: Query returns no results or table not found

Solutions:

  • Verify table exists before starting monitor
  • Check table name spelling
  • Include schema name: dbo.TableName
  • Use Wait Table first to wait for table creation

Connection Closed During Monitoring​

Error: Connection closed or lost during monitoring

Solutions:

  • Use credentials mode for dedicated connection
  • Ensure connection timeout is sufficient
  • Check network stability
  • Implement reconnection logic

Never Triggers​

Problem: Node waits indefinitely

Solutions:

  • Verify table name is correct
  • Check if columns are actually being added
  • Query INFORMATION_SCHEMA manually to verify
  • Implement timeout logic
  • Verify permissions on INFORMATION_SCHEMA
  • Ensure ALTER permission exists if you're adding columns

Performance Considerations​

Database Load​

  • Each poll executes a query against INFORMATION_SCHEMA
  • Faster polling = more queries
  • Consider database load when setting poll time
  • INFORMATION_SCHEMA queries are typically lightweight

Long-Running Monitoring​

  • For extended monitoring, use credentials mode
  • Monitor connection health
  • Consider implementing heartbeat checks
  • Plan for connection timeouts

Multiple Monitors​

  • Multiple Wait Column nodes increase database load
  • Consider consolidating monitoring logic
  • Use appropriate poll intervals
  • Monitor database performance

Integration Patterns​

Schema Version Control​

Wait Column (new version column added)
  ↓
Query (get schema version from new column)
  ↓
JavaScript (validate version)
  ↓
Update Application Configuration
  ↓
Restart Services

Automated Testing​

Non Query (deploy schema change)
  ↓
Wait Column (verify column added)
  ↓
Non Query (insert test data)
  ↓
Query (validate new column data)
  ↓
Test Passed/Failed

Progressive Schema Deployment​

Loop (over deployment steps):
  Non Query (add column)
  ↓
  Wait Column (verify added)
  ↓
  Test Column
  ↓
  If success: Continue
  If failure: Rollback

Troubleshooting​

Verify Table Exists​

-- Check table exists before monitoring
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = '{{tableName}}'
  AND TABLE_SCHEMA = '{{schema}}'

List Current Columns​

-- See current columns in table
SELECT
  COLUMN_NAME,
  DATA_TYPE,
  CHARACTER_MAXIMUM_LENGTH,
  IS_NULLABLE,
  ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = '{{schema}}'
  AND TABLE_NAME = '{{tableName}}'
ORDER BY ORDINAL_POSITION

Monitor Column Count​

-- Monitor column count manually
SELECT
  COUNT(*) AS ColumnCount,
  GETDATE() AS CheckTime
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = '{{schema}}'
  AND TABLE_NAME = '{{tableName}}'

Verify Permissions​

-- Check permissions on INFORMATION_SCHEMA
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE 1 = 0
-- If this fails, you don't have permission

Column Change Scenarios​

New Column Added​

ALTER TABLE dbo.Customers
ADD PhoneNumber NVARCHAR(20) NULL

Wait Column detects this and triggers.

Multiple Columns Added​

ALTER TABLE dbo.Orders
ADD
  ShippedDate DATETIME NULL,
  TrackingNumber NVARCHAR(50) NULL

Wait Column triggers when first column is added (count increases by 1).

Column Modification (Not Detected)​

ALTER TABLE dbo.Products
ALTER COLUMN Price DECIMAL(18,2)

Wait Column does NOT trigger - column count unchanged.

Column Dropped (Not Detected)​

ALTER TABLE dbo.Customers
DROP COLUMN OldColumn

Wait Column does NOT trigger - only detects additions.

Best Practices​

Pre-Monitoring Validation​

Query (verify table exists)
  ↓
Query (get initial column count)
  ↓
JavaScript (validate prerequisites)
  ↓
Wait Column (start monitoring)

Post-Trigger Verification​

Wait Column (triggered)
  ↓
Query (get all columns)
  ↓
JavaScript (identify new column)
  ↓
Query (test new column is accessible)
  ↓
Proceed with Processing

Timeout Implementation​

Start Timer
  ↓
Parallel:
  Branch 1: Wait Column
  Branch 2: Wait (timeout duration)
  ↓
First to complete triggers
  ↓
Handle Result or Timeout

See Also​