shanescott.sql-data-exporter 1.1.0

SQL Table Export CLI Tool

A .NET CLI tool that connects to SQL Server, inspects table structure, retrieves data, and exports everything to a structured JSON file with metadata and data rows.

Features

• 🔍 Complete Metadata Extraction: Captures table schema, column definitions, primary keys, foreign keys, and more
• 📊 Data Export: Exports table data as JSON with proper type conversion
• 🔄 Data Migration: Import JSON exports back into SQL Server with flexible column mapping
• 🎯 Flexible Filtering: Support for row limits and WHERE clause filters
• ⚙️ Multiple Configuration Options: Connection string via CLI, environment variable, or config file
• 🚀 Easy to Use: Simple command-line interface with sensible defaults

Installation

Install as a Global Tool

# From NuGet.org (Recommended)
dotnet tool install --global shanescott.sql-data-exporter

# Or from local build
dotnet pack
dotnet tool install --global --add-source ./nupkg shanescott.sql-data-exporter

Build from Source

cd sql-table-export
dotnet build
dotnet run -- --help

# Or use the installed tool
shanescott.sql-data-exporter --help

Usage

The tool provides two main commands:

• export - Export SQL Server table data to JSON
• migrate - Import JSON exports back into SQL Server

Export Command

# Export a table (uses environment variable or config file for connection)
shanescott.sql-data-exporter export --table users

# Specify connection string
shanescott.sql-data-exporter export -c "Server=localhost;Database=MyDb;Integrated Security=true;" -t users

# With schema
shanescott.sql-data-exporter export -t employees -s hr

# With custom output path
shanescott.sql-data-exporter export -t customers -o ./exports/customers.json

Migration Commands

# Generate SQL INSERT script from export JSON
shanescott.sql-data-exporter migrate generate \
  --config migration-config.json \
  --output-sql output/insert-script.sql

# Execute migration directly to database
shanescott.sql-data-exporter migrate execute \
  --config migration-config.json \
  --connection "Server=.;Database=TargetDB;Trusted_Connection=True;"

Advanced Export Usage

# Limit number of rows
shanescott.sql-data-exporter export -t orders -l 100

# With WHERE filter
shanescott.sql-data-exporter export -t customers -w "country = 'USA' AND status = 'active'"

# Combined options
shanescott.sql-data-exporter export \
  -c "Server=prod-db;Database=Sales;User Id=sa;Password=***;" \
  -t transactions \
  -s sales \
  -l 1000 \
  -w "date >= '2025-01-01'" \
  -o ./reports/sales-transactions.json

Data Migration

The migration feature allows you to import exported JSON data back into SQL Server with flexible column mapping and data transformation.

Migration Workflow

1. Export source table:

   shanescott.sql-data-exporter export \
     --connection "Server=.;Database=SourceDB;Trusted_Connection=True;" \
     --schema dbo \
     --table users \
     --output SQL/data/dbo.users.json
   

2. Create migration config (see examples in examples/ directory)

3. Generate SQL script (optional, for review):

   shanescott.sql-data-exporter migrate generate \
     --config my-migration.json \
     --output-sql output/migration.sql
   

4. Execute migration:

   shanescott.sql-data-exporter migrate execute \
     --config my-migration.json \
     --connection "Server=.;Database=TargetDB;Trusted_Connection=True;"
   

Migration Configuration

Create a JSON configuration file that describes how to map source columns to target columns:

{
  "sourceExport": "SQL/data/dbo.users.json",
  "targetSchema": "dbo",
  "targetTable": "users",
  "ignoreIdentity": true,
  "columnMappings": [
    {
      "source": "userId",
      "target": "id",
      "transform": "int"
    },
    {
      "source": "userName",
      "target": "username",
      "transform": "string"
    },
    {
      "source": "createdDate",
      "target": "created_at",
      "transform": "datetime"
    }
  ],
  "defaults": {
    "migrated_at": "now_utc",
    "migration_source": "legacy_system"
  }
}

Transform Types

Special Features

• Auto-mapping: Leave columnMappings empty to auto-generate 1:1 mappings
• Identity columns: Set ignoreIdentity: true to skip identity columns
• Default values: Add columns not in source with defaults
• now_utc function: Use "now_utc" as a default value for current UTC timestamp
• Transactions: Execute command uses transactions and rolls back on errors
• Progress reporting: Shows progress every 500 rows during execution

Example Configurations

See the examples/ directory for:

• basic.migration.json - Simple 1:1 migration
• advanced.migration.json - With transforms and defaults
• column-rename.migration.json - Column renaming example

Connection String Options

1. CLI Option (Highest Priority)

shanescott.sql-data-exporter -c "Server=localhost;Database=MyDb;Integrated Security=true;" -t users

2. Environment Variable

# Windows PowerShell
$env:SQL_CONNECTION_STRING = "Server=localhost;Database=MyDb;Integrated Security=true;"

# Windows CMD
set SQL_CONNECTION_STRING=Server=localhost;Database=MyDb;Integrated Security=true;

# Linux/Mac
export SQL_CONNECTION_STRING="Server=localhost;Database=MyDb;User Id=sa;Password=***;"

# Then run
shanescott.sql-data-exporter -t users

3. Configuration File (Lowest Priority)

Edit appsettings.json:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Database=MyDb;Integrated Security=true;"
  }
}

Command-Line Options

Export Command Options

Migrate Generate Command Options

Migrate Execute Command Options

Output Format

The tool generates a JSON file with the following structure:

{
  "metadata": {
    "schema": "dbo",
    "tableName": "users",
    "exportedAt": "2025-11-25T21:04:00Z",
    "rowCount": 150,
    "totalRowsInTable": 1000,
    "primaryKeys": ["userId"],
    "foreignKeys": [
      {
        "columnName": "departmentId",
        "referencedSchema": "dbo",
        "referencedTable": "departments",
        "referencedColumn": "id"
      }
    ]
  },
  "columns": [
    {
      "name": "userId",
      "dataType": "int",
      "maxLength": null,
      "precision": 10,
      "scale": 0,
      "isNullable": false,
      "isPrimaryKey": true,
      "isForeignKey": false,
      "defaultValue": null,
      "isIdentity": true
    },
    {
      "name": "username",
      "dataType": "nvarchar",
      "maxLength": 50,
      "precision": null,
      "scale": null,
      "isNullable": false,
      "isPrimaryKey": false,
      "isForeignKey": false,
      "defaultValue": null,
      "isIdentity": false
    }
  ],
  "data": [
    {
      "userId": 1,
      "username": "john_doe",
      "email": "john@example.com"
    },
    {
      "userId": 2,
      "username": "jane_smith",
      "email": "jane@example.com"
    }
  ]
}

Data Type Conversion

SQL Server types are converted to JSON-compatible types:

Examples

Export User Table

shanescott.sql-data-exporter -t users

Output: SQL/data/dbo.users.json

Export with Filter

shanescott.sql-data-exporter -t orders -w "status = 'pending' AND created_date > '2025-01-01'"

Export Limited Rows

shanescott.sql-data-exporter -t logs -l 500 -o ./recent-logs.json

Export from Different Schema

shanescott.sql-data-exporter -t employees -s hr -o ./hr-data/employees.json

Troubleshooting

Connection Issues

Problem: "No connection string provided"

# Solution: Provide connection string via any method
shanescott.sql-data-exporter -c "Server=localhost;..." -t users
# OR
$env:SQL_CONNECTION_STRING = "Server=localhost;..."

Problem: "Cannot open database"

# Solution: Check connection string and permissions
# Verify with sqlcmd:
sqlcmd -S localhost -d MyDb -E -Q "SELECT 1"

Table Not Found

Problem: "Table [dbo].[users] does not exist"

# Solution: Check table name and schema
shanescott.sql-data-exporter -t users -s dbo  # Specify correct schema

Permission Denied

Problem: "SELECT permission denied"

# Solution: Ensure SQL user has SELECT permission
# Grant permission:
GRANT SELECT ON [schema].[table] TO [username]

Requirements

• .NET 8.0 or later
• SQL Server 2012 or later (or Azure SQL Database)
• Appropriate database permissions (SELECT on target tables)

Development

Build

dotnet build

Run Tests

dotnet test

Create Package

dotnet pack -c Release

Install Locally

dotnet tool install --global --add-source ./nupkg shanescott.sql-data-exporter

Uninstall

dotnet tool uninstall --global shanescott.sql-data-exporter

Project Structure

sql-table-export/
├── Commands/
│   ├── ExportCommand.cs          # Export CLI command
│   └── MigrateCommand.cs         # Migration CLI commands
├── Configuration/
│   └── ConnectionStringProvider.cs  # Connection string resolution
├── Migration/
│   ├── TableExport.cs            # Export data models
│   ├── MigrationConfig.cs        # Migration config models
│   ├── SqlValueFormatter.cs      # SQL value formatting
│   └── MigrationService.cs       # Migration logic
├── Models/
│   ├── ColumnMetadata.cs         # Column metadata model
│   ├── ForeignKeyInfo.cs         # Foreign key model
│   ├── TableMetadata.cs          # Table metadata model
│   └── ExportResult.cs           # Export result model
├── Services/
│   ├── ISqlMetadataService.cs    # Metadata service interface
│   ├── SqlMetadataService.cs     # Metadata extraction
│   ├── IDataExportService.cs     # Export service interface
│   └── DataExportService.cs      # Data export logic
├── examples/
│   ├── basic.migration.json      # Basic migration example
│   ├── advanced.migration.json   # Advanced migration example
│   ├── column-rename.migration.json  # Column rename example
│   └── README.md                 # Migration examples guide
├── Program.cs                     # Entry point
├── appsettings.json              # Configuration file
└── sql-table-export.csproj       # Project file

License

MIT License

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

Future Enhancements

Export Features

• Support for multiple tables in one export
• Export to CSV, XML, or Parquet formats
• Support for PostgreSQL and MySQL
• Incremental exports
• Data masking/anonymization
• Progress bars for large exports

Migration Features

• Batch size configuration for execute command
• Support for MERGE statements (upsert)
• Support for UPDATE statements based on primary keys
• Parallel execution for large datasets
• Streaming JSON parser for very large files
• Schema validation before migration
• Dry-run mode for execute command
• Migration history tracking