Skip the SSIS Package - Just use another DBContext

Migration Missteps and a Return to the Core

For background, check out my previous post from November 4th. Here

My life is complicated because I made it complicated. I'm working on a MacBook Air—mostly for the battery life, and that’s enough said about that. I’ve done most of the development for this site and Miskowin on it, and honestly, it’s helped me get back to the core of development. These days, I mostly use VS Code instead of Visual Studio 2022. I do have Parallels set up to run a VM on my MacBook in case I need something special.

This is where things started to break down. I thought using an SSIS package would be the way to go for migrating data. So I jumped into the VM and got started. Welp—can't install the pgSQL drivers on an ARM-based PC. That’s a problem. And it’s not just a VM problem—anyone using Windows on ARM is going to hit that same roadblock. Ouch.

I pivoted to a proper x64 Windows machine. Installed everything—great start. Then I began setting up the connections. That’s where the documentation starts to fall apart. After four or five iterations, I still ran into issues connecting to PostgreSQL through the SSIS connection manager. Whether it was the type of connection or something else, it just wasn’t working.

At this point, I had to ask myself: Is this really worth the squeeze? I’m migrating a small number of users and their data. I could abandon the old data and start fresh, but I really want the experience of migrating between database types and doing it properly.

So I’m doing it in code this time.

I’m using EF Core, and the plan is to create a second DbContext that connects to the current Azure-hosted SQL database. Then I’ll write a service that copies data from each table into the new PostgreSQL-backed DbContext. It’ll include some error handling—and probably some error handling for me too.

EF Core Dual DbContext Setup

To migrate data from Azure SQL to PostgreSQL using EF Core, define two separate DbContext classes and wire them up with distinct connection strings.

// Azure SQL DbContext
public class AzureSqlDbContext : DbContext
{
    public AzureSqlDbContext(DbContextOptions<AzureSqlDbContext> options) : base(options) { }

    public DbSet<User> Users { get; set; }
}

// PostgreSQL DbContext
public class PostgresDbContext : DbContext
{
    public PostgresDbContext(DbContextOptions<PostgresDbContext> options) : base(options) { }

    public DbSet<User> Users { get; set; }
}
  

Registering in Program.cs

builder.Services.AddDbContext<AzureSqlDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("AzureSql")));

builder.Services.AddDbContext<PostgresDbContext>(options =>
    options.UseNpgsql(builder.Configuration.GetConnectionString("Postgres")));
  

Migration Service Example

public class MigrationService
{
    private readonly AzureSqlDbContext _source;
    private readonly PostgresDbContext _target;

    public MigrationService(AzureSqlDbContext source, PostgresDbContext target)
    {
        _source = source;
        _target = target;
    }

    public async Task MigrateUsersAsync()
    {
        var users = await _source.Users.ToListAsync();
        _target.Users.AddRange(users);
        await _target.SaveChangesAsync();
    }
}