SQL Bundle Builder

The SQL Bundle Builder is a report (80800) in Business Central that generates a complete ZIP package for SQL Server replication. It automates the creation of SQL table scripts, BCCL commands, mapping files, and run scripts, saving you significant setup time when replicating multiple tables.

Accessing the SQL Bundle Builder

You can access the SQL Bundle Builder from:

The BCCL page in Business Central: Tasks > SQL Bundle Builder
Search (Alt-Q): "SQL Bundle Builder"

What the Bundle Contains

The generated ZIP package includes everything needed to replicate selected tables from Business Central to SQL Server:

1. SQL CREATE TABLE Scripts

For each selected table, the builder generates a SQL CREATE TABLE statement with:

Proper column types mapped from BC field types
Primary key constraints matching the BC table's primary key
Column names derived from field names (or MappedName from mapping)

Example:

CREATE TABLE [Customer] (
    [No_] nvarchar(20) NOT NULL,
    [Name] nvarchar(100),
    [City] nvarchar(30),
    [Balance__LCY_] decimal(38,20),
    [Blocked] int,
    PRIMARY KEY ([No_])
)

2. RunBCCL.cmd (Incremental Sync)

A Windows batch script that runs BCCL for each table, designed for regular, scheduled execution. It starts with one --remember line that stores the S2S authentication, -f sql, the SQL connection string, and the web service URL for the task lines that follow. Then, for every selected table, it contains:

a GETDATA line with a SystemModifiedAt view filter (Field2000000003) derived from the RetentionDays report parameter, which picks up inserts and modifications, and
if AddDeletionsFromChangeLog is enabled, a CHANGELOG line filtered on the Change Log Entry's Date and Time field (Field2), which picks up deletions.

Simplified example of the generated lines (placeholders shown as the builder emits them):

BCCL --remember --auth S2S --clientid <clientid> --clientsecret <clientsecret> --tenantid <tenantid> -f sql --sqlconnect <SQLConnectionString> -w "<web service URL>"
BCCL -t changelog -s table=15 view=WHERE(Field2=1({today(-1D)}..))  -o [G_L_Account] -m mapping\G_L_Account.mapping.json
BCCL -t getdata -s table=15 view=WHERE(Field2000000003=1({today(-1D)}..))  -o [G_L_Account] -m mapping\G_L_Account.mapping.json
BCCL -t changelog -s table=17 view=WHERE(Field2=1({today(-1D)}..))  -o [G_L_Entry] -m mapping\G_L_Entry.mapping.json
BCCL -t getdata -s table=17 view=WHERE(Field2000000003=1({today(-1D)}..))  -o [G_L_Entry] -m mapping\G_L_Entry.mapping.json

RetentionDays produces a {today(-ND)} expression. The GETDATA lines need no Change Log at all; only the CHANGELOG lines depend on Change Log deletion tracking. Replace the <clientid>, <clientsecret>, <tenantid>, and <SQLConnectionString> placeholders before running the script, and keep the --remember line as a single unbroken line.

3. FirstRunBCCL.cmd (Initial Full Load)

A batch script for the initial full load of all tables. It starts with the same --remember line and then runs GETDATA for every table without a date filter, so all data is exported:

BCCL --remember --auth S2S --clientid <clientid> --clientsecret <clientsecret> --tenantid <tenantid> -f sql --sqlconnect <SQLConnectionString> -w "<web service URL>"
BCCL -t getdata -s table=15 -o [G_L_Account] -m mapping\G_L_Account.mapping.json
BCCL -t getdata -s table=17 -o [G_L_Entry] -m mapping\G_L_Entry.mapping.json

The generated script does not truncate the target tables. Because BCCL upserts on the primary key, re-running it refreshes existing rows; if you want a clean reload of a table, add --sqlprecommand "TRUNCATE TABLE [G_L_Account]" to that table's line yourself.

The Generated Scripts Are the Reference Implementation

FirstRunBCCL.cmd and RunBCCL.cmd are the reference implementation for command-line incremental replication. If you run BCCL manually or from Windows Task Scheduler, open these two files and copy the command patterns from them, including the filter syntax and the mapping file paths. They are plain BCCL.EXE commands: you can edit them, extend them with more tables, and schedule them directly. You do not have to use BC Service Tasks to get incremental sync, and for many tables or many companies the scripts are the recommended approach (see Service Tasks vs. Scripted Runs).

The scripts rely on the --remember header line for authentication. For very long-running tables, or if you see authentication or connection errors part-way through a transfer, copy the S2S parameters and -w onto the affected task lines so BCCL can renew the token during the run; see Running Long or Unattended Transfers Reliably. Do not use the remembered settings when running the scripts for several companies in parallel; see Replicating Multiple Companies.

4. Mapping Files

Individual mapping.json files for each table, containing:

Field numbers and names
MappedName for SQL column names
Validation settings
Dimension expansion configuration (if selected)
Format strings

5. Credential Setup Commands

Example commands for configuring authentication:

Basic authentication remember command
S2S authentication remember command
OAuth setup command

Report Parameters

When you run the SQL Bundle Builder, you configure these parameters:

Retention Period

Parameter	Description	Default
RetentionDays	How many days back to sync for incremental runs	1
RetentionMinutes	How many minutes back to sync	0

The retention period determines the lookback window for incremental sync. For example, with RetentionDays=1, the RunBCCL script processes changes from the last 24 hours: the generated GETDATA lines carry view=WHERE(Field2000000003=1({today(-1D)}..)), and the CHANGELOG lines carry the matching Field2=1({today(-1D)}..) filter on the Change Log Entry's Date and Time field.

Deletion Tracking

Parameter	Description
AddDeletionsFromChangeLog	Include deletion tracking via Change Log

When enabled, the generated CHANGELOG commands also process deletion entries, removing records from SQL Server that were deleted in Business Central.

Important: This requires that the Business Central Change Log has deletion tracking enabled for the target tables.

Dimension Expansion

The SQL Bundle Builder allows you to select dimensions to expand as SQL columns:

Choose which dimensions to include (e.g., DEPARTMENT, PROJECT, REGION)
Each selected dimension becomes an additional column in the SQL table
The mapping file includes DimensionFromSet() function entries for each dimension
During sync, dimension values are extracted from the Dimension Set ID and written to their own columns

Example: With DEPARTMENT dimension selected, the Customer table gets an extra Department column containing the department dimension value for each customer.

SQL Connection String

Specify the target SQL Server connection string. This is embedded in the generated scripts.

SQL Type Mapping

The SQL Bundle Builder uses the following type mapping when generating CREATE TABLE scripts:

BC Field Type	SQL Type	Notes
Text	nvarchar(length)	Length from BC field definition
Code	nvarchar(length)	Length from BC field definition
Date	date
DateTime	datetime
Decimal	decimal(38,20)	High precision for financial data
Integer	int
Option	int	Stored as numeric option value
BigInteger	bigint	Must be `bigint`; an `int` column overflows (see Common Issues)
Duration	bigint	Stored as milliseconds
Boolean	bit
GUID	uniqueidentifier
Blob	image	Binary data
Media	image	Binary data
MediaSet	image	Binary data
RecordID	varbinary(448)	BC record reference

Step-by-Step Usage

1. Open the SQL Bundle Builder

From the BCCL page, select SQL Bundle Builder under Tasks.

2. Select Tables

Choose the Business Central tables you want to replicate to SQL Server. Common choices include:

Customer (18)
Vendor (23)
Item (27)
G/L Entry (17)
Cust. Ledger Entry (21)
Vendor Ledger Entry (25)
Sales Header/Line (36/37)
Purchase Header/Line (38/39)

3. Configure Retention

Set the retention period based on your sync frequency:

Sync Frequency	Recommended Retention
Every 1-5 minutes	30 minutes
Every 15 minutes	2 hours
Hourly	1 day
Daily	2 days

Always set retention longer than your sync interval to ensure no changes are missed.

4. Enable Deletion Tracking

If you need SQL Server to reflect deletions made in BC, enable AddDeletionsFromChangeLog. Make sure the BC Change Log is configured to track deletions for all selected tables.

5. Select Dimensions

If you need dimension values as SQL columns, select the relevant dimensions. This is particularly useful for financial reporting where dimension breakdowns are essential.

6. Set SQL Connection

Enter your SQL Server connection string.

7. Generate and Download

Click OK to generate the bundle. Download the ZIP file.

8. Deploy

Extract the ZIP to your BCCL installation folder
Review the generated scripts and mapping files
Run the SQL CREATE TABLE scripts on your SQL Server to create the target tables
Run FirstRunBCCL.cmd for the initial full data load
Schedule RunBCCL.cmd for ongoing incremental synchronization

Primary Key Handling

The SQL Bundle Builder creates SQL tables with primary key constraints that match the Business Central table's primary key:

Simple keys: Single column primary key
Composite keys: Multi-column primary keys (common in ledger entry tables)

The upsert logic during sync uses these primary keys to determine whether to insert or update each record.

Tips and Best Practices

Generate fresh bundles when adding new tables to your replication set
Set retention periods conservatively (longer than your sync interval)
Always enable deletion tracking for tables where records may be deleted
Review generated SQL scripts before executing them on production databases
Use dimension expansion only for dimensions you actually need in reports
Run FirstRunBCCL.cmd during off-peak hours for large tables
Keep the generated mapping files; they are needed for both initial and incremental sync