SQL Server作业自动化SSIS包遇问题：PostgreSQL数据抽取至SQL Server

Hey there, this is such a common gotcha when moving from local SSIS debugging to SQL Server Agent jobs—let’s walk through the most likely culprits and fixes based on your setup:

1. SQL Server Agent runs 64-bit by default (but your ODBC is 32-bit) #

Your local SSDT debug uses 32-bit mode to match your 32-bit PostgreSQL ODBC driver, but SQL Server Agent defaults to a 64-bit process. That means it can’t see the 32-bit DSN you configured in C:\Windows\SysWOW64\odbcad.exe.

Fix:

• Edit your SQL Server Agent job step
• Go to the Execution Options tab
• Check the box labeled "Use 32-bit runtime"
• Save the job and re-run it

2. Agent Service Account Permissions & DSN Scope #

When debugging locally, you’re using your own user account—this account has access to your user-specific DSN and PostgreSQL credentials. But SQL Server Agent runs under a dedicated service account (often NT SERVICE\SQLSERVERAGENT or a custom domain account) that doesn’t have the same access.

Fixes:

• Switch to a System DSN: Instead of a User DSN, create a 32-bit System DSN via C:\Windows\SysWOW64\odbcad.exe. System DSNs are visible to all accounts on the server, including the Agent service account.
• Grant PostgreSQL Access: Make sure the Agent service account has a valid login in PostgreSQL, with SELECT permissions on the tables you’re extracting.
• File/Path Permissions: If your SSIS package is stored on the file system, ensure the Agent account has read access to that folder.

3. Connection String or Package Configuration Issues #

If your SSIS package uses a User DSN (instead of a System DSN) or hardcodes paths/credentials tied to your local user, the Agent account won’t be able to resolve these.

Fixes:

• Use a DSN-less Connection String: Instead of relying on a DSN, configure your PostgreSQL connection manager with a direct connection string. Example:

  Driver={PostgreSQL Unicode};Server=your-pg-server;Port=5432;Database=your-db;Uid=your-user;Pwd=your-password;
  

• Validate Package Configurations: If you’re using SSIS configurations (like XML files or environment variables), ensure the Agent account can access and read those configurations. For SSIS Catalog-deployed packages, double-check that environment variables are mapped correctly.

4. Missing 32-bit ODBC Driver on the Server #

If your SQL Server is on a different machine than your local SSDT setup, you might have forgotten to install the 32-bit PostgreSQL ODBC driver on the server. Even if you installed it locally, the Agent needs the driver present on its own server.

Fix:

• Install the same version of the 32-bit PostgreSQL ODBC driver on the SQL Server machine. During installation, choose the option to install for all users to ensure the Agent account can access it.

Critical Troubleshooting Step: Check Agent Job History #

Before diving into fixes, always check the detailed error message in the SQL Server Agent job history. It will tell you exactly what’s failing—whether it’s a missing driver, permission error, or invalid DSN. This will narrow down your troubleshooting path drastically.

You can also test running the package manually as the Agent service account:

1. Log into the SQL Server machine using the Agent service account
2. Open a command prompt and run the package with the 32-bit dtexec utility:

   "C:\Program Files (x86)\Microsoft SQL Server\150\DTS\Binn\dtexec.exe" /f "C:\Path\To\Your\Package.dtsx"
   

This will replicate the Agent’s execution context and help you spot issues that don’t show up in local debugging.

内容的提问来源于stack exchange，提问作者Valouf