如何在SSIS项目中构建C#脚本组件实现OAuth 2.0访问？

核心思路 #

优先根据API支持的OAuth 2.0授权类型选择实现方案：

• 服务端到服务端无用户交互场景：用客户端凭证模式（client_credentials），这是SSIS自动化任务的首选
• 需要用户交互的场景：用授权码模式（authorization_code），仅适合手动触发的SSIS包

具体实现步骤与代码示例 #

1. 添加脚本组件 #

在SSIS包中添加脚本组件（作为数据源选「源组件」，作为预处理选「脚本任务」），选择C#作为脚本语言。

2. 实现OAuth 2.0获取Token #

以下是客户端凭证模式的代码示例，需替换为你的API实际配置：

using System.Net.Http;
using System.Text;
using System.Text.Json;
using System.Data.SqlClient;
using System.Collections.Generic;

// 定义Token响应模型，匹配API返回的JSON结构
public class TokenResponse
{
    [JsonPropertyName("access_token")]
    public string AccessToken { get; set; }
    [JsonPropertyName("token_type")]
    public string TokenType { get; set; }
    [JsonPropertyName("expires_in")]
    public int ExpiresIn { get; set; }
}

public override void CreateNewOutputRows()
{
    // OAuth 2.0配置（建议通过SSIS包配置/环境变量传入，不要硬编码）
    string tokenEndpoint = "https://your-api-domain/token";
    string clientId = "your-client-id";
    string clientSecret = "your-client-secret";
    string scope = "required-scope"; // 部分API不需要可省略

    // 获取Access Token
    string accessToken = GetOAuthToken(tokenEndpoint, clientId, clientSecret, scope);

    // 调用API获取数据
    List<YourDataModel> apiData = FetchApiData(accessToken);

    // 将数据写入Azure SQL
    WriteToAzureSql(apiData);
}

private string GetOAuthToken(string tokenUrl, string clientId, string clientSecret, string scope)
{
    using (HttpClient client = new HttpClient())
    {
        var requestParams = new FormUrlEncodedContent(new[]
        {
            new KeyValuePair<string, string>("grant_type", "client_credentials"),
            new KeyValuePair<string, string>("client_id", clientId),
            new KeyValuePair<string, string>("client_secret", clientSecret),
            new KeyValuePair<string, string>("scope", scope)
        });

        var response = client.PostAsync(tokenUrl, requestParams).Result;
        response.EnsureSuccessStatusCode();

        var tokenResp = response.Content.ReadFromJsonAsync<TokenResponse>().Result;
        return tokenResp.AccessToken;
    }
}

3. 调用API获取数据 #

// 定义数据模型，匹配API返回的字段结构
public class YourDataModel
{
    public string Id { get; set; }
    public string Name { get; set; }
    public decimal Amount { get; set; }
    // 按需添加其他字段
}

private List<YourDataModel> FetchApiData(string accessToken)
{
    using (HttpClient client = new HttpClient())
    {
        // 添加Bearer Token到请求头
        client.DefaultRequestHeaders.Authorization = 
            new System.Net.Http.Headers.AuthenticationHeaderValue("Bearer", accessToken);

        var apiResponse = client.GetAsync("https://your-api-domain/data-endpoint").Result;
        apiResponse.EnsureSuccessStatusCode();

        var jsonContent = apiResponse.Content.ReadAsStringAsync().Result;
        return JsonSerializer.Deserialize<List<YourDataModel>>(jsonContent);
    }
}

4. 写入Azure SQL Server #

private void WriteToAzureSql(List<YourDataModel> dataList)
{
    // Azure SQL连接字符串（建议通过SSIS包配置传入）
    string connString = "Server=tcp:your-sql-server.database.windows.net,1433;Initial Catalog=your-db;Persist Security Info=False;User ID=your-user;Password=your-pwd;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";

    using (SqlConnection conn = new SqlConnection(connString))
    {
        conn.Open();
        foreach (var item in dataList)
        {
            string insertSql = @"INSERT INTO TargetTable (Id, Name, Amount) 
                                VALUES (@Id, @Name, @Amount)";

            using (SqlCommand cmd = new SqlCommand(insertSql, conn))
            {
                cmd.Parameters.AddWithValue("@Id", item.Id);
                cmd.Parameters.AddWithValue("@Name", item.Name);
                cmd.Parameters.AddWithValue("@Amount", item.Amount);
                cmd.ExecuteNonQuery();
            }
        }
    }
}

关键注意事项 #

• 敏感信息保护：clientId、clientSecret、数据库连接字符串必须通过SSIS包配置、环境变量或Azure Key Vault存储，绝对不能硬编码在脚本中
• Token缓存：根据expires_in值缓存Token，避免每次请求都调用Token端点，提升性能
• 异常处理：添加try-catch块捕获网络异常、API错误、数据库操作异常，并写入SSIS日志（使用Dts.Log或脚本组件的日志功能）
• HttpClient复用：生产环境建议复用HttpClient实例，避免频繁创建释放导致的Socket资源耗尽
• 授权码模式适配：如果API仅支持授权码模式，可在脚本中嵌入WebBrowser控件引导用户登录获取授权码，再交换Token，但该模式不适合无人值守的自动化任务

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