使用SSIS与C#批量下载文件遇0字节及请求错误求助

Hey there, let's work through this SSIS file download issue you're hitting! That 0-byte file and "remote server returned an error (0) bad request" message almost always points to issues with how your HTTP request is structured, missing required headers, or problems with variable references. Let's break this down step by step.

Common Causes & Fixes #

1. Missing Required HTTP Headers #

Most modern web servers block requests that don't include basic browser-like headers (like User-Agent or Accept). Without these, the server rejects your request with a 400 Bad Request, leading to empty files.

2. Invalid or Incomplete Variable References #

Your code snippet cuts off at Dts.Variabl...—make sure you're correctly referencing your variables and that they hold valid values (e.g., User::vlink is a working URL, $Project::DownloadPath is an existing, writable directory).

3. Unhandled Redirects or Authentication #

Some URLs redirect to other locations, or require authentication (Basic Auth, tokens) that your script isn't handling.

Corrected C# Script (Using WebClient) #

Here's a robust version of your script that addresses these issues, with error handling and proper request formatting:

public void Main()
{
    bool fireAgain = true;
    try
    {
        // Validate and retrieve variables
        string fileUrl = Dts.Variables["User::vlink"].Value.ToString();
        string downloadRootPath = Dts.Variables["$Project::DownloadPath"].Value.ToString();
        string fileName = System.IO.Path.GetFileName(fileUrl);
        string fullSavePath = System.IO.Path.Combine(downloadRootPath, fileName);

        using (System.Net.WebClient client = new System.Net.WebClient())
        {
            // Add browser-like headers to avoid being blocked
            client.Headers.Add("User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/118.0.0.0 Safari/537.36");
            client.Headers.Add("Accept", "text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8");
            
            // Enable auto-redirects for URLs that redirect
            client.AllowAutoRedirect = true;

            // Optional: Add authentication if required
            // client.Credentials = new System.Net.NetworkCredential("your-username", "your-password");

            // Download the file
            client.DownloadFile(fileUrl, fullSavePath);

            Dts.Events.FireInformation(0, "Download Success", $"Successfully saved file to: {fullSavePath}", string.Empty, 0, ref fireAgain);
            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
    catch (System.Exception ex)
    {
        string errorDetails = $"Error Message: {ex.Message}";
        if (ex.InnerException != null)
        {
            errorDetails += $"\nInner Exception: {ex.InnerException.Message}";
        }
        Dts.Events.FireError(0, "Download Failed", errorDetails, string.Empty, 0);
        Dts.TaskResult = (int)ScriptResults.Failure;
    }
}

// Keep the ScriptResults enum (auto-generated in SSIS script tasks)
enum ScriptResults
{
    Success = 0,
    Failure = 1
}

Alternative: Using HttpClient (Modern Approach) #

If WebClient still gives you trouble, try HttpClient—it's more flexible for complex HTTP scenarios:

public void Main()
{
    bool fireAgain = true;
    try
    {
        string fileUrl = Dts.Variables["User::vlink"].Value.ToString();
        string downloadRootPath = Dts.Variables["$Project::DownloadPath"].Value.ToString();
        string fileName = System.IO.Path.GetFileName(fileUrl);
        string fullSavePath = System.IO.Path.Combine(downloadRootPath, fileName);

        using (System.Net.Http.HttpClient client = new System.Net.Http.HttpClient())
        {
            // Configure request headers
            client.DefaultRequestHeaders.UserAgent.ParseAdd("Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/118.0.0.0 Safari/537.36");
            client.DefaultRequestHeaders.Accept.ParseAdd("*/*");

            // Send request and ensure success
            System.Net.Http.HttpResponseMessage response = client.GetAsync(fileUrl).Result;
            response.EnsureSuccessStatusCode();

            // Write stream to file
            using (System.IO.Stream responseStream = response.Content.ReadAsStreamAsync().Result)
            using (System.IO.FileStream fileStream = new System.IO.FileStream(fullSavePath, System.IO.FileMode.Create, System.IO.FileAccess.Write))
            {
                responseStream.CopyTo(fileStream);
            }

            Dts.Events.FireInformation(0, "Download Success", $"Successfully saved file to: {fullSavePath}", string.Empty, 0, ref fireAgain);
            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
    catch (System.Exception ex)
    {
        string errorDetails = $"Error Message: {ex.Message}";
        if (ex.InnerException != null)
        {
            errorDetails += $"\nInner Exception: {ex.InnerException.Message}";
        }
        Dts.Events.FireError(0, "Download Failed", errorDetails, string.Empty, 0);
        Dts.TaskResult = (int)ScriptResults.Failure;
    }
}

enum ScriptResults
{
    Success = 0,
    Failure = 1
}

Additional Troubleshooting Steps #

• Test the URL Manually: Copy the value of User::vlink into a browser or Postman—if it doesn't download there, the issue is with the URL itself (expired, requires login, etc.).
• Verify Permissions: Ensure the account running the SSIS package has write access to $Project::DownloadPath.
• Check for Certificate Issues: If downloading from an HTTPS site with a self-signed certificate, add this line before making the request (only for testing—use proper certificate validation in production):

  System.Net.ServicePointManager.ServerCertificateValidationCallback += (sender, cert, chain, sslPolicyErrors) => true;
  

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