Azure Data Factory for SQL Server 2025

This module has referenced Azure Data Factory (ADF) at several points already as the cloud side of the loading story this course has otherwise kept on-premises: a lift-and-shift target for an existing SSIS package, and the first step many teams take before considering a full rebuild in a native Fabric Data Factory pipeline. This lesson covers the two real paths from a SQL Server 2025 database into ADF, what each one actually produces, and when to reach for which.

Both paths start from the same place this course already covered: the SQL Server Import and Export Wizard, launched as DTSWizard.exe, still carrying that name as a holdover from the DTS-to-SSIS rename this course established back when it introduced dtexec. The wizard itself hasn't changed, but what happens after it finishes has two genuinely different answers depending on whether an SSIS package is the point of the exercise or just an accident of how the wizard happens to work.

Running the Import and Export Wizard from SSMS or Visual Studio builds an SSIS package behind the scenes whether anyone asks for one or not. Rather than letting the wizard run that transfer immediately and throw the package away, its Save and Run Package screen can save the result instead, and that saved package is what actually moves into Azure:

1. Generate and save the package. On the Save and Run Package screen, select Save SSIS Package, either to the file system as a .dtsx file or to SQL Server, instead of leaving Run Immediately as the only option.
2. Provision an Azure-SSIS Integration Runtime. Inside the Azure Data Factory instance, an Azure-SSIS IR is a managed cluster of Azure virtual machines dedicated to running .dtsx packages. This is the same Integration Runtime this course has already referenced as the standard lift-and-shift path for an existing on-premises SSIS project, the one built with SSDT two lessons ago, that needs to keep running without a redesign.
3. Deploy the package. Deploy the saved .dtsx file to the SSISDB catalog, hosted on Azure SQL Database or Azure SQL Managed Instance, the same project deployment model this course covered when it introduced the catalog, or store the file in Azure Files or Blob Storage for the older, file-based deployment model instead.
4. Trigger it from a pipeline. An Execute SSIS Package activity, placed inside an ADF pipeline, schedules, parameterizes, and runs the deployed package, in the same way a SQL Server Agent job step has already covered doing it on-premises.

Two things are worth knowing before going this route. First, 32-bit execution mode is deprecated in SQL Server 2025 Integration Services, and the wizard, the SSIS engine, and SSMS 21 all run 64-bit only. Any driver a wizard-generated package depends on, an Excel or Access source, or a third-party ODBC or OLE DB provider, needs a genuine 64-bit build installed on the Azure-SSIS IR's own nodes, or the package simply fails to run there even though it ran fine on a workstation that still had an older 32-bit driver lying around.

Second, "DTS" in DTSWizard.exe is a naming holdover, not a sign that the actual 1990s-era Data Transformation Services engine is involved. That original DTS, and its .dts package format, has been gone since SQL Server 2005, replaced by SSIS, exactly as this course established earlier in the module. A package saved out of today's wizard is a modern SSIS package start to finish; nothing about moving it into Azure involves resurrecting anything from that era.

For the same guided, point-and-click table-copying experience the wizard provides, without standing up and paying for a dedicated Azure-SSIS IR cluster, ADF's own Copy Data tool, launched directly from the ADF Studio home page, covers the same ground natively, inside Azure, with no SSIS package involved at all:

• Selecting source and destination connections, called Linked Services in ADF, mirrors picking a source and destination in the wizard; a SQL Server 2025 instance can be either side, reached through a Self-Hosted Integration Runtime when it's still sitting on-premises.
• Picking one or more tables, or writing a custom query, and mapping columns, with destination tables created automatically when they don't already exist, follows the same flow as the wizard's own table-mapping screen.
• Instead of producing a .dtsx file, the tool generates a native ADF pipeline containing a Copy Activity, with a ForEach loop wrapped around it when more than one table is being copied in a single run. That pipeline runs on the standard Azure Integration Runtime, serverless, with nothing to provision, when both ends are already in Azure, or on a Self-Hosted Integration Runtime, a small agent installed on a machine with network access to the source, when the pipeline needs to reach back into an on-premises SQL Server 2025 instance.

Nothing about this path touches SSIS, SSDT, or the SSISDB catalog. A pipeline built this way is edited, run, and monitored entirely inside ADF Studio, a web interface rather than the SSMS and Visual Studio tooling the first option depends on, which is itself one of the clearer signals for which option actually fits a given job.

The table below lines the two paths up against each other directly. Neither one is a strict upgrade over the other; they solve the same basic problem, moving data or a package into Azure, from two different starting assumptions about whether an SSIS package is wanted at all.

The dividing line is really whether an SSIS package, with everything this course has already covered about building one in SSDT, already exists or is genuinely worth building for the job at hand. If it does, or if it should, the first option carries that investment into Azure rather than discarding it. If the job is simple enough that a package was never really the point, hauling one unused connector across tables, the second option gets the same table-to-table copy done without taking on an SSIS deployment to maintain afterward.

Whichever path a package or pipeline takes into Azure, the connection it makes back to SQL Server 2025 doesn't have to rely on a stored SQL login and password sitting in a connection string. The Microsoft Entra ID authentication this course already covered for SSIS's own ADO.NET connections, interactive sign-in during development, a managed identity for the Azure-SSIS IR or the ADF pipeline itself at runtime, or a service principal for an automated deployment, works the same way for an Execute SSIS Package activity or a Copy Data tool pipeline. A managed identity in particular removes the need to store any credential at all: ADF authenticates as itself, and SQL Server 2025 grants that identity exactly the permissions the job needs, the same least-privilege principle this course applied to proxy accounts when it covered SQL Server Agent.

Pairing that with the strict encryption and TLS 1.3 support this course already established for SQL Server 2025's own connections keeps a cloud-triggered load no less secure than one run entirely on-premises. A Self-Hosted Integration Runtime reaching back through a firewall to an on-premises instance is the one piece worth double-checking in this picture: it needs outbound access to Azure, not inbound access from it, which usually means no firewall rule has to open a hole for Azure to reach in.

An ADF pipeline, whichever option built it, is monitored from the Monitor tab inside ADF Studio: every pipeline run, its status, start and end time, and, for a failed run, the actual error message from the activity that failed. That's the ADF-side equivalent of the SSISDB catalog's own execution views, catalog.executions and catalog.operation_messages, that this course covered when it introduced the catalog, and of the Agent job history this course relied on when it covered scheduling a package on-premises. A pipeline built around the first option still has both logs available: ADF's own run history for the pipeline shell, and the catalog's execution details for what happened inside the package itself once the Execute SSIS Package activity handed off to it.

For either option, Azure Monitor can also collect ADF's pipeline metrics and send an alert when a run fails or runs longer than expected, the cloud equivalent of the Database Mail notification this course set up on a SQL Server Agent job step. A nightly pipeline failing quietly at 2 a.m. is exactly as much of a problem in Azure as it is on-premises, and the fix is the same: don't rely on checking in the morning to find out.

An ADF pipeline, under either option, still needs something to run it on a recurring basis rather than by hand, the same problem SQL Server Agent solved for a package running on-premises. ADF covers this with triggers rather than Agent jobs, and the shapes on offer parallel the schedule types this course already covered:

• A schedule trigger runs a pipeline on a wall-clock recurrence, daily, hourly, or on specific days of the week, the direct equivalent of the recurring daily schedule this course set up for the Nightly SalesOrders Import job in the previous lesson.
• A tumbling window trigger runs a pipeline over a fixed, non-overlapping time slice, every hour on the hour, for instance, and tracks each window's own run independently, including the ability to retry or backfill one specific missed window without rerunning everything else. Nothing in this course's on-premises scheduling has a direct equivalent to this; it's a genuinely ADF-specific tool for exactly the case where each period's data load has to be accounted for on its own.
• An event-based trigger starts a pipeline the moment a file lands in a storage account, rather than on any clock at all, useful when the actual signal that data is ready has nothing to do with time of day.

A pipeline built around the Copy Data tool is usually paired with a schedule trigger from the start, since the tool itself offers to create one during setup. A pipeline wrapping an Execute SSIS Package activity can use any of the three, and the choice follows the same reasoning this course applied to Agent schedules: match the trigger to what the job is actually waiting on, a clock, a window of time, or a file, rather than defaulting to whichever one happens to be first in the list.

Both options in this lesson are explicitly lift-and-shift moves: they get an existing package, or an existing table's data, into Azure with little or no redesign, which is exactly why this course has referenced them as the first step most teams take rather than the last. Microsoft Fabric's own Data Factory, built on the same pipeline and Copy Activity concepts as Azure Data Factory but integrated into Fabric's broader analytics platform, is the native rebuild this course has mentioned without yet covering: taking a package that's been running on an Azure-SSIS IR for a while and rebuilding its logic as a Fabric pipeline directly, retiring the IR cluster, and the SSIS dependency along with it, once the transformation logic is simple enough to be worth rebuilding rather than carrying forward indefinitely.

That rebuild is a deliberate, separate decision, not something either option here does automatically, and it isn't always worth making. A package depending on a transformation SSIS handles well and Fabric has no clean equivalent for is a legitimate reason to keep it running on an Azure-SSIS IR rather than rebuilding it just to retire one piece of infrastructure.

Suppose the SalesOrdersArchive load this course has built up across the module, first as a single statement, then through BULK INSERT and bcp, then as the ETL_SalesData_Import SSIS package this course built in SSDT and scheduled with SQL Server Agent, now also needs to feed a reporting database running in Azure SQL Database. Two genuinely different ways to get there follow directly from the two options covered above.

Taking the first option, ETL_SalesData_Import doesn't need to be rebuilt at all: deploying it to an SSISDB catalog hosted on the same Azure SQL Database, provisioning an Azure-SSIS IR sized for its workload, and adding an Execute SSIS Package activity to a new ADF pipeline reuses everything already designed and tested for that package, proxy-account connections and validation logic included, with no redesign beyond pointing it at a catalog that now lives in Azure.

Taking the second option instead, because the actual requirement turned out to be nothing more than keeping SalesOrdersArchive current in the reporting database, with none of the transformation logic the package was built to handle, the Copy Data tool maps SalesOrders directly to a matching table in Azure SQL Database in a few minutes, a Self-Hosted Integration Runtime bridging the on-premises source, no SSIS package, catalog, or Integration Runtime cluster required at all.

Either result is monitored the same way: a scheduled trigger on the ADF pipeline, the Monitor tab for run status, and, following the same load-then-validate discipline this course has applied after every method covered in this module, a row-count check against the destination table before trusting that the data actually arrived rather than just that the pipeline reported success.

Between the Import and Export Wizard, BULK INSERT, bcp, SSIS built with SSDT, scheduling with SQL Server Agent, and now Azure Data Factory, this module has covered the full range of ways data actually moves into and out of a SQL Server 2025 database, from a single ad hoc transfer to a scheduled, monitored pipeline running partly or entirely in Azure. That range maps closely to the "plan and implement a migration strategy" skills area the DP-300 (Azure Database Administrator Associate) exam has already come up under at several points across this module, since a real migration almost never settles on just one of these tools; it typically starts with a wizard-generated transfer or a bulk load, grows into a scheduled SSIS package once the job needs to repeat reliably, and eventually needs an answer, one of the two covered in this lesson, for what happens once part of the environment itself moves to Azure.

None of that makes the choice between Option 1 and Option 2 a one-time decision made once and left alone, either. A package built to preserve complex transformation logic today is still a legitimate Option 1 candidate next year; a job that turns out to need nothing more than a straight table copy is a candidate for retiring an Azure-SSIS IR that was never really earning its keep. The judgment this module keeps coming back to, picking the simplest tool that actually does the job, applies here exactly as it did back at the first lesson's single INSERT statement.