如何在SSIS中通过原生方式（如C#脚本组件）对接需动态Token认证的API并将数据导入SQL Server

Got it, let's tackle your two main questions step by step, using native SSIS components (no third-party tools needed) as you requested.

一、自动获取并复用动态API Token #

Since your token refreshes daily, we'll use a package-level variable to store it, a Script Task to fetch it at the start of the package, and then reuse it in subsequent API calls.

Step 1: Create a Package-Level Variable #

1. In your SSIS package, go to the Variables pane (right-click anywhere in the package designer > Variables).
2. Create a new variable named @APIToken with data type String, set its scope to the entire package so all components can access it.

Step 2: Use a Script Task to Fetch the Token #

Add a Script Task to the Control Flow, configure it to write to the @APIToken variable (go to Script Task Editor > ReadWriteVariables > select User::APIToken). Then open the script editor and use this C# code:

using System;
using System.Net.Http;
using System.Collections.Generic;
using Microsoft.SqlServer.Dts.Runtime;

public partial class ScriptMain : VSTARTScriptObjectModelBase
{
    public void Main()
    {
        // Replace with your actual credentials and auth API URL
        string staticUsername = "your_username";
        string staticPassword = "your_password";
        string authEndpoint = "https://your-auth-api.com/get-token";

        try
        {
            using (HttpClient client = new HttpClient())
            {
                // Adjust the request content to match your API's expected format (form-data, JSON, etc.)
                var authPayload = new FormUrlEncodedContent(new[]
                {
                    new KeyValuePair<string, string>("username", staticUsername),
                    new KeyValuePair<string, string>("password", staticPassword)
                });

                var response = client.PostAsync(authEndpoint, authPayload).Result;
                response.EnsureSuccessStatusCode(); // Throws if auth fails

                // Parse the response to extract the token (adjust JSON path to match your API's response)
                string responseJson = response.Content.ReadAsStringAsync().Result;
                dynamic tokenResponse = Newtonsoft.Json.JsonConvert.DeserializeObject(responseJson);
                string token = tokenResponse.access_token; // Replace with your token field name

                // Save token to the package variable
                Dts.Variables["User::APIToken"].Value = token;
                Dts.TaskResult = (int)DtsExecResult.Success;
            }
        }
        catch (Exception ex)
        {
            // Fire an error event so SSIS logs the issue
            Dts.Events.FireError(0, "Token Fetch Failed", ex.Message, string.Empty, 0);
            Dts.TaskResult = (int)DtsExecResult.Failure;
        }
    }
}

Step 3: Reuse the Token in API Data Requests #

Add a Data Flow Task after the Script Task. Inside the Data Flow, use a Script Component as your data source:

1. In the Script Component Editor, go to ReadOnlyVariables and select User::APIToken so the component can read the token.
2. Go to the Input and Outputs tab (we'll come back to this for column definition later).
3. Open the script editor and use this code to attach the token to every API request:

using System;
using System.Net.Http;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

public class ScriptMain : UserComponent
{
    private string _apiToken;
    private HttpClient _httpClient;

    public override void PreExecute()
    {
        base.PreExecute();
        // Grab the token from the package variable
        _apiToken = Variables.APIToken;
        _httpClient = new HttpClient();
        // Add the token to the default request headers so every call uses it
        _httpClient.DefaultRequestHeaders.Authorization = new System.Net.Http.Headers.AuthenticationHeaderValue("Bearer", _apiToken);
    }

    public override void CreateNewOutputRows()
    {
        string dataEndpoint = "https://your-data-api.com/get-data";

        try
        {
            var response = _httpClient.GetAsync(dataEndpoint).Result;
            response.EnsureSuccessStatusCode();

            string dataJson = response.Content.ReadAsStringAsync().Result;
            // Parse the data array (adjust to match your API's response structure)
            dynamic[] dataItems = Newtonsoft.Json.JsonConvert.DeserializeObject<dynamic[]>(dataJson);

            foreach (var item in dataItems)
            {
                // Add a new row to the output buffer
                Output0Buffer.AddRow();
                // Map API fields to your output columns (we'll define these next)
                Output0Buffer.Id = item.id;
                Output0Buffer.CustomerName = item.customer_name;
                Output0Buffer.OrderDate = DateTime.Parse(item.order_date);
                // Repeat for all fields you need to extract
            }
        }
        catch (Exception ex)
        {
            bool fireAgain = true;
            ComponentMetaData.FireError(0, "Data Fetch Failed", ex.Message, string.Empty, 0, ref fireAgain);
        }
    }

    public override void PostExecute()
    {
        base.PostExecute();
        // Clean up resources
        _httpClient?.Dispose();
    }
}

二、定义对应的数据列 #

Back in the Script Component Editor (the one used as data source), go to the Input and Outputs tab:

1. Under Outputs, select Output 0 (or rename it to something meaningful like API_Data_Output).
2. Click Add Column to create each column you need to extract from the API response.
3. For each column, set the DataType to match the API's data type:
   • Use DT_I4 for integers, DT_STR/DT_WSTR for strings, DT_DBTIMESTAMP for dates, etc.
   • Make sure the column name in the script (like Output0Buffer.Id) matches the name you define here.

Once your columns are defined, the Script Component will pass this data to downstream components (like an OLE DB Destination) where you can map it to your SQL Server table columns.

Key Notes #

• JSON Library: If you're using older SSIS versions (pre-2019), you might need to include Newtonsoft.Json in your script component (add it via NuGet and copy the DLL to the SSIS script folder). For SSIS 2019+, you can use System.Text.Json instead.
• Token Expiry: Since the token is daily, the Script Task runs once at package start. If your package runs longer than the token's validity, add logic to re-fetch the token if you get a 401 Unauthorized response.
• Error Handling: Extend the try/catch blocks to handle retries, network issues, or invalid data formats as needed.

内容的提问来源于stack exchange，提问作者Hessam P.