The SQL Server Import Wizard is designed for one-off migrations. Anything that repeats — a daily sales file, a nightly feed from a partner system, an hourly queue drain — needs a different approach. The combination of PowerShell and SQL Server Agent handles the three problems that make recurring imports unreliable when done manually: human error, forgotten runs, and no audit trail when something goes wrong.

This guide builds a complete pipeline: a PowerShell script that checks for the file, imports it using BULK INSERT, archives the processed file, and signals success or failure — and a SQL Server Agent job that runs it on a schedule. The permissions section is not optional reading: Agent automation fails almost exclusively because of access configuration mistakes, not script errors.

Why PowerShell Handles the Parts T-SQL Can't

T-SQL is excellent at querying and transforming data inside the database. It is not designed for file system operations. BULK INSERT requires the file to already be at the right path when the statement runs — it cannot check whether the file arrived, move it afterward, or send an alert if it was missing. PowerShell can do all three, and Invoke-Sqlcmd lets it execute the T-SQL import as a single step within the same script.

Task split between PowerShell and T-SQL

Responsibility	Handled by	How
Check the file arrived	PowerShell	Test-Path — exits gracefully instead of failing if missing
Load data into SQL Server	T-SQL via PowerShell	Invoke-Sqlcmd executes BULK INSERT from within the script
Archive the processed file	PowerShell	Move-Item renames with a timestamp and moves to an archive folder
Report failure to SQL Agent	PowerShell	exit 1 signals a non-zero exit code — Agent marks the job as failed
Schedule the entire pipeline	SQL Server Agent	Runs the PowerShell script on any frequency via a step of type PowerShell
Send failure alerts	SQL Server Agent	Notifications tab — email on failure via Database Mail

The Script: What Each Block Does

The script is structured as five sequential blocks. Each block has a single job and produces a specific outcome. Keeping this structure separates the logic cleanly so that any failure is immediately locatable — you know which block failed without reading through an error stack trace.

Script variables to configure before deploying

Variable	What to set it to	Example
$Server	SQL Server instance name or IP. Use .\SQLEXPRESS for a local named instance	PROD-DB-01
$Database	The database that contains the target table	SalesOperations
$CSVPath	Full path to the file as it will appear to the SQL Server Agent service account	C:\Imports\NewData\DailySales.csv
$ArchivePath	Folder where processed files are moved after a successful import	C:\Imports\Archive\
$CurrentDate	Timestamp format appended to the archived filename to prevent overwrites	yyyyMMdd_HHmm
$Sqlquery	The BULK INSERT statement. Update the table name and WITH options to match your schema	See BULK INSERT guide for full option reference

File check (Test-Path)

Test-Path returns true or false for a given file path. If it returns false — the file hasn't arrived yet — the script exits cleanly with a log message rather than throwing an error. This is the most important guard in the script: without it, Invoke-Sqlcmd tries to run BULK INSERT against a non-existent path, which produces a confusing "cannot open file" error in SQL Server instead of a clear "file was missing" note in your PowerShell log.

Import (Invoke-Sqlcmd + BULK INSERT)

Invoke-Sqlcmd connects to SQL Server and executes the BULK INSERT statement. The -ErrorAction Stop flag is critical: by default, Invoke-Sqlcmd treats SQL errors as warnings and continues — -ErrorAction Stop forces it to throw a terminating exception on any SQL error, which gets caught by the try/catch block. Without this flag, a failed import silently reports success and the file gets moved to Archive anyway.

Archive (Move-Item)

After a confirmed successful import, Move-Item renames the file with a timestamp (Sales_20241027_0600.csv) and moves it to the archive folder. The timestamp prevents overwrites when the same base filename is used for daily deliveries. The file only moves inside the try block — a failed import leaves the original file in the input folder so the next run can attempt it again.

Error handling (catch + exit 1)

The catch block captures any exception thrown by Invoke-Sqlcmd and writes it to the PowerShell error stream. exit 1 is not optional — it's how PowerShell signals to SQL Server Agent that the job step failed. Without exit 1, even a caught and logged error produces an exit code of 0, and SQL Agent marks the job step as succeeded. Use Write-Error rather than Write-Host for error messages so they appear in the Agent job history output.

Logging

Add Out-File to any Write-Host or Write-Error line to persist the output. For example: Write-Host "Import Successful at $(Get-Date)" | Out-File -Append -FilePath "C:\Logs\ImportLog.txt". A persistent log lets you answer "what happened on Wednesday" without relying on Agent job history, which has limited retention. Log the filename, row count (from the BULK INSERT result), and timestamp at minimum.

Invoke-Sqlcmd requires the SqlServer module

The Invoke-Sqlcmd cmdlet is not part of the PowerShell standard library — it ships with SQL Server Management Studio and is also available via Install-Module -Name SqlServer from the PowerShell Gallery. On the machine running SQL Server Agent, install it with: Install-Module SqlServer -AllowClobber -Force. Run this once as Administrator. If the module isn't present, the script fails silently from the Agent's perspective — which is why the -ErrorAction Stop flag matters.

Configuring the SQL Server Agent Job

The Agent job is the scheduler. It stores the path to the script, defines when to run it, and manages the success/failure outcome — including email notifications if you configure them. The UI is inside SSMS under the SQL Server Agent node; the important settings across each tab are listed below.

Tab	Field	Value to set
General	Name	Descriptive with source and frequency — e.g., Daily_SalesCSV_Import
General	Category	Data Collector (or create a custom category for all import jobs)
Steps → New Step	Step name	Run PowerShell Import Script
Steps → New Step	Type	PowerShell — not "Operating system (CmdExec)"
Steps → New Step	Command	& "C:\Scripts\ImportSales.ps1" (the & operator invokes the script file)
Steps → New Step	On success / On failure	Go to next step / Quit with failure
Schedules → New Schedule	Frequency	Daily, weekly, or recurring — match to when the source file arrives
Schedules → New Schedule	Start time	15–30 minutes after the file is expected, to allow for delivery delays
Notifications	Email operator	When the job fails — requires Database Mail configured with an operator
Notifications	Write to Windows event log	When the job fails — useful if email isn't configured

Test the job before activating the schedule

Right-click the job → Start Job at Step to trigger it immediately as the Agent service account. This is the only way to confirm the script runs correctly under the Agent's identity, not just under yours. Most permission failures only surface when running via Agent — a job that works when you manually run the script from a PowerShell window can fail under Agent because the service account doesn't have the same file access your profile does.

Permissions: The Most Common Point of Failure

Two different service accounts are involved in this pipeline, and they each need different access. Conflating them is the source of most "it works from my desktop but fails in the job" problems.

Account	What it needs	Where to grant it
SQL Server Agent service account	Read + Write on the import folder	Folder → Security → Edit → Add account → grant Read/Write
SQL Server Agent service account	Read + Write on the archive folder	Same as above, on the archive folder path
SQL Server service account (MSSQLSERVER)	Read on the import folder	Required separately — BULK INSERT reads via the SQL Server process, not PowerShell
SQL Server login used by Invoke-Sqlcmd	INSERT + ADMINISTER BULK OPERATIONS on the target database	SSMS → Security → Logins → [login] → Server Roles: bulkadmin

Two accounts, not one

The SQL Server Agent service account (which runs the PowerShell script) and the SQL Server service account (which executes the BULK INSERT file read) are separate accounts with separate permission requirements. A common misconfiguration grants the Agent account both file permissions but forgets the SQL Server account's Read access on the folder — the script runs, Invoke-Sqlcmd fires, and then BULK INSERT fails with OS Error 5 (Access Denied) because the SQL Server process can't reach the file. See the BULK INSERT troubleshooting guide for the full OS Error 5 diagnosis workflow.

How to find both service account names

Open Windows Services (services.msc). Find SQL Server (MSSQLSERVER) — the "Log On As" column shows the SQL Server service account. Find SQL Server Agent (MSSQLSERVER) — that's the Agent account. On named instances, the service names include the instance name in parentheses. Both service accounts need to be added to the import folder's Security settings, with the permissions in the table above.

Three Design Decisions That Prevent Pipeline Failures

Import into a staging table, not directly into production

Automated imports from an external feed should land in a staging table first — an unconstrained table with all NVARCHAR(MAX) columns and no foreign keys. Run validation queries against staging (check for negative prices, null IDs, date range anomalies) before promoting rows to production with INSERT INTO … SELECT or MERGE. A bad file imported directly into a production table is a support incident. A bad file imported into staging is a discarded row set. The staging pattern also isolates the BULK INSERT performance concerns from the transactional concerns of the production table — see the schema evolution and staging guide for how to structure this.

Set up email alerts on failure, not on success

SQL Server Agent can email on both success and failure. Alert on failure only. Success-alerting produces noise that trains you to ignore the email; a single "job failed" alert in an otherwise quiet inbox gets immediate attention. Under the job's Notifications tab, check Email, select your operator, and set the dropdown to "When the job fails." This requires Database Mail to be configured on the instance — a one-time setup that takes about 15 minutes in the SQL Server Configuration Wizard.

Separate the import and archive into two job steps

Rather than combining file check, import, and archive in one PowerShell script, consider splitting them: Step 1 runs the import script, Step 2 (only reached on Step 1 success) runs the archive script. This gives you a cleaner failure record — you can see in job history whether the import itself failed, or whether the import succeeded but the archive move failed. The "On success, go to step 2 / On failure, quit with failure" flow control in the Steps tab handles this without any code changes to the scripts.

Scaling Beyond a Single File

The pipeline above handles a fixed filename. Real environments often need to process any CSV that lands in a folder, not just DailySales.csv. Replace the single $CSVPath variable with a Get-ChildItem loop that iterates over all *.csv files in the input folder, runs the import for each, and archives each processed file individually. This also lets you handle late arrivals naturally: any file that lands before the next scheduled run will be picked up without any change to the job configuration.

For tables where the schema of the incoming CSV may vary between files — new columns added, column order changed — the staging table approach combined with the schema evolution pattern handles this without manual intervention. The minimal logging guide covers how to configure the database recovery model in the Agent job itself so that BULK INSERT performance is preserved even on an automated schedule.

Automating CSV Imports in SQL Server with PowerShell and SQL Agent Jobs