TSQL/CLR Wrapper for SQL Server

The TSQL/CLR wrapper allows you to execute FastTransfer directly from SQL Server using T-SQL, enabling seamless integration of cross-database data transfers within your SQL Server stored procedures, jobs, and scripts.

Overview

The FastTransfer TSQL wrapper is a SQL Server CLR (Common Language Runtime) assembly that exposes FastTransfer functionality as T-SQL stored procedures and functions.

Benefits:

• Execute transfers directly from T-SQL code
• Integrate with SQL Server Agent jobs
• No need for external scheduling tools
• Centralized management within SQL Server
• Error handling within T-SQL try/catch blocks

Repository

The TSQL/CLR wrapper source code and documentation are available on GitHub:

🔗 FastTransfer TSQL Wrapper on GitHub

Requirements

SQL Server Configuration

CLR integration must be enabled on your SQL Server instance:

-- Enable CLR integration
sp_configure 'clr enabled', 1;
RECONFIGURE;
GO

-- For assemblies requiring external access
sp_configure 'clr strict security', 0;
RECONFIGURE;
GO

Security Considerations

Enabling CLR and relaxing CLR strict security can introduce security risks. Ensure you understand the implications and follow your organization's security policies.

Supported Versions

• SQL Server 2016 and later
• Azure SQL Managed Instance (with CLR enabled)
• SQL Server on Linux (2017+)

Prerequisites

• .NET Framework 4.7.2 or later (Windows)
• .NET Core 3.1+ (Linux)
• FastTransfer binary accessible from SQL Server machine
• Appropriate database permissions (see below)

Installation

Step 1: Download the Wrapper

Clone or download the wrapper from GitHub:

git clone https://github.com/aetperf/FastTransfer-TSQL-Wrapper.git

Step 2: Build the Assembly

Build the CLR assembly using Visual Studio or the .NET CLI:

cd FastTransfer-TSQL-Wrapper
dotnet build -c Release

This produces FastTransferWrapper.dll.

Step 3: Deploy to SQL Server

Deploy the assembly to your SQL Server database:

-- Create the assembly
CREATE ASSEMBLY FastTransferWrapper
FROM 'C:\Path\To\FastTransferWrapper.dll'
WITH PERMISSION_SET = UNSAFE;
GO

-- Create the stored procedure wrapper
CREATE PROCEDURE dbo.sp_ExecuteFastTransfer
    @CommandLine NVARCHAR(MAX),
    @FastTransferPath NVARCHAR(500) = 'C:\Tools\FastTransfer\FastTransfer.exe'
AS EXTERNAL NAME FastTransferWrapper.[FastTransferWrapper.StoredProcedures].ExecuteFastTransfer;
GO

Permission Set

The wrapper requires PERMISSION_SET = UNSAFE because it executes external processes. This requires appropriate SQL Server permissions.

Step 4: Configure FastTransfer Path

Update the default FastTransfer executable path or pass it as a parameter:

-- Option 1: Pass path each time
EXEC dbo.sp_ExecuteFastTransfer 
    @CommandLine = '--sourceconnectiontype pgsql --sourceserver pg.example.com ...',
    @FastTransferPath = 'C:\CustomPath\FastTransfer.exe';

-- Option 2: Use default path (set in procedure)
EXEC dbo.sp_ExecuteFastTransfer 
    @CommandLine = '--sourceconnectiontype pgsql --sourceserver pg.example.com ...';

Usage

Basic Transfer

Execute a simple table transfer:

DECLARE @Command NVARCHAR(MAX) = 
    '--sourceconnectiontype pgsql ' +
    '--sourceserver pg.example.com ' +
    '--sourceuser pguser ' +
    '--sourcepassword pgpass ' +
    '--sourcedatabase sales ' +
    '--sourcetable orders ' +
    '--targetconnectiontype mssql ' +
    '--targetserver localhost ' +
    '--targettrusted ' +
    '--targetdatabase warehouse ' +
    '--targettable staging_orders';

EXEC dbo.sp_ExecuteFastTransfer @CommandLine = @Command;

Transfer with Error Handling

Wrap the call in a try/catch block:

BEGIN TRY
    DECLARE @Command NVARCHAR(MAX) = 
        '--sourceconnectiontype mysql ' +
        '--sourceserver mysql.example.com:3306 ' +
        '--sourceuser mysqluser ' +
        '--sourcepassword mysqlpass ' +
        '--sourcedatabase ecommerce ' +
        '--sourcetable products ' +
        '--targetconnectiontype mssql ' +
        '--targetserver localhost ' +
        '--targettrusted ' +
        '--targetdatabase warehouse ' +
        '--targettable products ' +
        '--loadmode Truncate';

    EXEC dbo.sp_ExecuteFastTransfer @CommandLine = @Command;
    
    PRINT 'Transfer completed successfully';
END TRY
BEGIN CATCH
    PRINT 'Transfer failed: ' + ERROR_MESSAGE();
    -- Log error or take corrective action
    THROW;
END CATCH

SQL Server Agent Job Integration

Create a job step that uses the wrapper:

-- Create a SQL Server Agent job
USE msdb;
GO

EXEC dbo.sp_add_job
    @job_name = N'Daily PostgreSQL to SQL Server Transfer',
    @enabled = 1;
GO

EXEC dbo.sp_add_jobstep
    @job_name = N'Daily PostgreSQL to SQL Server Transfer',
    @step_name = N'Transfer Orders',
    @subsystem = N'TSQL',
    @database_name = N'ETL',
    @command = N'
DECLARE @Command NVARCHAR(MAX) = 
    ''--sourceconnectiontype pgsql '' +
    ''--sourceserver pg.example.com '' +
    ''--sourceuser pguser '' +
    ''--sourcepassword pgpass '' +
    ''--sourcedatabase sales '' +
    ''--query "SELECT * FROM orders WHERE order_date >= CURRENT_DATE - INTERVAL ''''1 day''''" '' +
    ''--targetconnectiontype mssql '' +
    ''--targetserver localhost '' +
    ''--targettrusted '' +
    ''--targetdatabase warehouse '' +
    ''--targettable daily_orders '' +
    ''--loadmode Truncate'';

EXEC dbo.sp_ExecuteFastTransfer @CommandLine = @Command;
';
GO

EXEC dbo.sp_add_schedule
    @schedule_name = N'Daily at 2 AM',
    @freq_type = 4, -- Daily
    @freq_interval = 1,
    @active_start_time = 020000; -- 02:00:00 AM
GO

EXEC dbo.sp_attach_schedule
    @job_name = N'Daily PostgreSQL to SQL Server Transfer',
    @schedule_name = N'Daily at 2 AM';
GO

EXEC dbo.sp_add_jobserver
    @job_name = N'Daily PostgreSQL to SQL Server Transfer',
    @server_name = N'(LOCAL)';
GO

Dynamic Parameter Building

Build command parameters dynamically:

CREATE PROCEDURE dbo.TransferTableFromPostgreSQL
    @SourceServer NVARCHAR(100),
    @SourceDatabase NVARCHAR(100),
    @SourceTable NVARCHAR(100),
    @TargetDatabase NVARCHAR(100),
    @TargetTable NVARCHAR(100)
AS
BEGIN
    DECLARE @Command NVARCHAR(MAX);
    
    SET @Command = 
        '--sourceconnectiontype pgsql ' +
        '--sourceserver ' + @SourceServer + ' ' +
        '--sourceuser pguser ' +
        '--sourcepassword pgpass ' +
        '--sourcedatabase ' + @SourceDatabase + ' ' +
        '--sourcetable ' + @SourceTable + ' ' +
        '--targetconnectiontype mssql ' +
        '--targetserver localhost ' +
        '--targettrusted ' +
        '--targetdatabase ' + @TargetDatabase + ' ' +
        '--targettable ' + @TargetTable;
    
    EXEC dbo.sp_ExecuteFastTransfer @CommandLine = @Command;
END
GO

-- Usage
EXEC dbo.TransferTableFromPostgreSQL
    @SourceServer = 'pg.example.com',
    @SourceDatabase = 'sales',
    @SourceTable = 'customers',
    @TargetDatabase = 'warehouse',
    @TargetTable = 'customers';

Advanced Features

Logging Integration

Integrate with SQL Server logging tables:

CREATE TABLE dbo.FastTransferLog (
    LogID INT IDENTITY PRIMARY KEY,
    TransferName NVARCHAR(200),
    CommandLine NVARCHAR(MAX),
    StartTime DATETIME2,
    EndTime DATETIME2,
    Status NVARCHAR(50),
    ErrorMessage NVARCHAR(MAX)
);
GO

CREATE PROCEDURE dbo.LoggedFastTransfer
    @TransferName NVARCHAR(200),
    @CommandLine NVARCHAR(MAX)
AS
BEGIN
    DECLARE @StartTime DATETIME2 = GETUTCDATE();
    DECLARE @LogID INT;
    
    INSERT INTO dbo.FastTransferLog (TransferName, CommandLine, StartTime, Status)
    VALUES (@TransferName, @CommandLine, @StartTime, 'Running');
    
    SET @LogID = SCOPE_IDENTITY();
    
    BEGIN TRY
        EXEC dbo.sp_ExecuteFastTransfer @CommandLine = @CommandLine;
        
        UPDATE dbo.FastTransferLog
        SET EndTime = GETUTCDATE(),
            Status = 'Completed'
        WHERE LogID = @LogID;
    END TRY
    BEGIN CATCH
        UPDATE dbo.FastTransferLog
        SET EndTime = GETUTCDATE(),
            Status = 'Failed',
            ErrorMessage = ERROR_MESSAGE()
        WHERE LogID = @LogID;
        
        THROW;
    END CATCH
END
GO

Parallel Multiple Transfers

Execute multiple transfers in sequence:

CREATE PROCEDURE dbo.TransferMultipleTables
AS
BEGIN
    DECLARE @Tables TABLE (TableName NVARCHAR(100));
    
    INSERT INTO @Tables VALUES 
        ('customers'),
        ('orders'),
        ('products'),
        ('order_items');
    
    DECLARE @TableName NVARCHAR(100);
    DECLARE TableCursor CURSOR FOR SELECT TableName FROM @Tables;
    
    OPEN TableCursor;
    FETCH NEXT FROM TableCursor INTO @TableName;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT 'Transferring table: ' + @TableName;
        
        EXEC dbo.TransferTableFromPostgreSQL
            @SourceServer = 'pg.example.com',
            @SourceDatabase = 'production',
            @SourceTable = @TableName,
            @TargetDatabase = 'warehouse',
            @TargetTable = @TableName;
        
        FETCH NEXT FROM TableCursor INTO @TableName;
    END
    
    CLOSE TableCursor;
    DEALLOCATE TableCursor;
END
GO

Security Best Practices

Use Dedicated Service Account

Create a dedicated Windows service account for FastTransfer operations:

-- Create SQL Server login for service account
CREATE LOGIN [DOMAIN\FastTransferService] FROM WINDOWS;
GO

USE TargetDatabase;
GO

CREATE USER [DOMAIN\FastTransferService] FOR LOGIN [DOMAIN\FastTransferService];
GO

-- Grant necessary permissions
GRANT INSERT, UPDATE, DELETE, SELECT ON SCHEMA::staging TO [DOMAIN\FastTransferService];
GO

Encrypt Credentials

Instead of storing passwords in T-SQL code:

1. Use Windows Authentication where possible (--targettrusted)
2. Store credentials in SQL Server credentials:

CREATE CREDENTIAL FastTransferPGCredential
WITH IDENTITY = 'pguser',
SECRET = 'pgpass';
GO

3. Use Azure Key Vault or similar for cloud deployments

Restrict Assembly Permissions

Limit which users can execute the wrapper:

-- Create role for FastTransfer users
CREATE ROLE FastTransferUsers;
GO

-- Grant execute permission
GRANT EXECUTE ON dbo.sp_ExecuteFastTransfer TO FastTransferUsers;
GO

-- Add users to role
ALTER ROLE FastTransferUsers ADD MEMBER [User1];
GO

Troubleshooting

Assembly Cannot Be Created

Error: "CREATE ASSEMBLY failed because it could not open the physical file"

Solution:

• Verify file path is accessible from SQL Server service account
• Check file permissions
• Copy DLL to a location accessible by SQL Server

Permission Denied Executing FastTransfer

Error: "Permission denied executing external process"

Solution:

• Verify SQL Server service account has execute permissions on FastTransfer.exe
• Check that FastTransfer.exe path is correct
• Ensure CLR is configured with appropriate trust level

CLR Not Enabled

Error: "CLR integration is not enabled"

Solution:

sp_configure 'clr enabled', 1;
RECONFIGURE;

Performance Considerations

• FastTransfer process runs synchronously (T-SQL waits for completion)
• For very long-running transfers, consider using SQL Server Agent jobs
• Monitor SQL Server process memory when running multiple transfers
• Large transfers may impact SQL Server performance

Further Information

For complete source code, examples, and updates:

🔗 Visit the GitHub Repository

For FastTransfer documentation:

• CLI Parameters
• Parallel Processing
• Examples