VSTS SQL脚本部署报错：GetDirectoryName参数无效，路径格式不合法

Let’s walk through the most likely causes and fixes for this frustrating error you’re hitting when switching from .dacpac to script-based deployment:

1. Check for Invalid Path Characters or Malformed File/Folder Names #

First, rule out simple path-related issues:

• Look for special characters like ?, <, >, |, or non-ASCII characters in your SQL file names or their parent directories—these can break the task’s path parsing logic.
• Watch out for trailing/leading spaces in folder or file names (they’re easy to overlook but often cause path validation failures).
• Ensure your artifact structure doesn’t create ambiguous relative paths (e.g., empty folder names or nested paths that resolve to nothing).

2. Fix Your "SQL Scripts" Path Configuration #

The wildcard or relative path you’re using might be resolving to an unexpected location. Try these adjustments:

• Use a fully rooted path starting with $(System.DefaultWorkingDirectory) (the standard variable for the agent’s working folder). For example:

  $(System.DefaultWorkingDirectory)/YourPublishedArtifactName/**/*.sql
  

  Instead of just **/*.sql—the task might be trying to resolve the path relative to a directory that doesn’t contain your scripts.
• For single files, skip wildcards entirely and use the full artifact path:

  $(System.DefaultWorkingDirectory)/YourPublishedArtifactName/Customer.sql
  

  Wildcards can introduce ambiguity if there are duplicate file names in different subfolders.

3. Verify Your Artifact’s Structure and Availability #

• Head to your release’s Artifacts tab and confirm the published artifact actually contains the SQL files you’re targeting. Sometimes builds fail to include all files, or the artifact download step might miss them.
• Add a quick Command Line task right before your deploy step to run:

  dir $(System.DefaultWorkingDirectory) /s
  

  This will list every file the agent can access, letting you confirm the exact paths of your SQL scripts.

4. Check Task Version and Agent Compatibility #

• Make sure you’re using the latest version of the SQL Server Database Deploy task (check the version dropdown in the release pipeline editor)—older versions had known path-parsing bugs.
• If using a self-hosted agent:
  • On Windows, verify you’re not hitting the default 260-character path limit (enable long paths in Windows settings if your scripts are in a deep directory structure).
  • Ensure the agent service account has full access to the working directory and SQL files.

5. Test with a Minimal Script #

To isolate the issue:

• Create a simple test script (e.g., Test.sql) with just PRINT 'Deployment test'
• Include this script in your published artifact
• Configure the deploy task to target this single file using the full rooted path
• If this works, the problem is specific to your original SQL files or their paths; if it still fails, the issue lies with the task configuration or agent setup.

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