you haven't explained very much, but typically you know the limits of your columns and truncate them when you parse the csv data to meet your column requirements.

import csv

max_lengths = {
    'field1': 255,
    'field2': 1024,
    # add fields and their max sizes
}

def truncate_row(row):
    for field, max_len in max_lengths.items():
        if field in row and len(row[field]) > max_len:
            row[field] = row[field][:max_len]
    return row

with open('data.csv', newline='', encoding='utf-8') as csvfile:
    reader = csv.DictReader(csvfile)
    rows = []
    for row in reader:
        truncated_row = truncate_row(row)
        rows.append(truncated_row)
        # Insert truncated_row into SQL DB safely here

Select allOpen in new window

Stage each file into a wide staging table using VARCHAR(MAX) or NVARCHAR(MAX) via BULK INSERT or OPENROWSET, then profile lengths with LEN or DATALENGTH to find columns exceeding target sizes. Redirect offenders to an exceptions table using ERRORFILE or a Conditional Split, clean or approve truncation with LEFT, then load the final typed table in a single transaction.