Cannot load script for execution error while executing SSIS Script Task

Question

I am trying to run a script task (C# Code) in SSIS (Visual Studio 2022). When I run this C# code separately in visual studio .net console, it runs fine. But when I put same code in Script Task it gives me error : "Cannot load script for execution".

My TargetServerVersion is inline with the sql server I have installed. My .Net version in 4.7. My SQL server version is 2019. And when I look for SSIS Projects in Visual studio, it says it is 2022 (Version 1.0.1).

Problem which I see is that "Microsoft.SqlServer.Dts.Runtime.dll" isn't present on my machine at all.

Not sure what should I look at exactly. Please help me with the solution.

Attaching below my C# Code :

class ApiDataFetcher { // Define a class that matches the structure of the API response public class ApiResponse {

    public string A1 { get; set; }
    public string A2 { get; set; }
    public string A3 { get; set; }
    public string A4 { get; set; }
    public string A5 { get; set; }
    public string A6 { get; set; }
    public string A7 { get; set; }
    public string A8 { get; set; }
    public string A9 { get; set; }
    public string A10 { get; set; }

    // Add other properties that match the API response structure
}

// Call the API and return the data as a list of ApiResponse objects
public static async Task<List<ApiResponse>> GetDataFromApiAsync(string apiUrl)
{
    using (HttpClient client = new HttpClient())
    {
        var response = await client.GetStringAsync(apiUrl);
        var data = JsonConvert.DeserializeObject<List<ApiResponse>>(response); // Deserialize the response
        return data;
    }
}

private static async Task<string> FetchDataFromApi()
{
         string apiUrl = "<ApiURL>";
         string apiUsername = "<Username>";
         string apiPassword = "<Password>";
         string apiKey = "<API_Key>";
    
    using (var client = new HttpClient())

    {
        // Optionally use Basic Auth if needed
        var authToken = Convert.ToBase64String(Encoding.ASCII.GetBytes($"{apiUsername}:{apiPassword}"));
        client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Basic", authToken);

        // Create JSON content for the body
        var jsonContent = new
        {
            api_key = apiKey
            // Add other parameters here if needed
        };
        var content = new StringContent(JsonConvert.SerializeObject(jsonContent), Encoding.UTF8, "application/json");


        // Display headers for debugging
        //Console.WriteLine("HttpClient Headers:");
        foreach (var header in client.DefaultRequestHeaders)
        {
            Console.WriteLine($"{header.Key}: {string.Join(", ", header.Value)}");
        }

        // Send POST request with the JSON body
        var response = await client.PostAsync(apiUrl, content);
        response.EnsureSuccessStatusCode();

        // Read response content as string
        return await response.Content.ReadAsStringAsync();
    }
}


static async Task Main(string[] args)
{
    // Replace with your API URL
    try
    {
        var apiData = await FetchDataFromApi();

        // Deserialize the JSON response using the wrapper class
        var jsonObject = JsonConvert.DeserializeObject<YourJsonWrapper>(apiData);

        // Displaying the API data (optional)
        // foreach (var item in apiData)
        //{
        //  Console.WriteLine($"ID: {item.Id}, Data: {item.Data}");
        //}

        // You can now pass this data to SSIS or to a SQL Server table
        InsertDataToSqlServer(jsonObject.data); // Placeholder method to insert data into SQL Server
    }
    catch (Exception ex)
    {
        Console.WriteLine("Error: " + ex.Message);
    }
}

// Placeholder for inserting data into a SQL Server table
public class YourJsonWrapper
{
    public int status { get; set; }
    public List<ApiResponse> data { get; set; }
}
public static void InsertDataToSqlServer(List<ApiResponse> apiData)
{
    string connectionString = "Data Source=<Server Name>; Initial Catalog=<DB Name>; Integrated Security=True;";

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();

        foreach (var record in apiData)
        {
            string query = "INSERT INTO Test1 (A1, A2, A3, A4, A5, A6, A7, A8, A9, A10) VALUES (@Value1, @Value2, @Value3, @Value4, @Value5, @Value6, @Value7, @Value8, @Value9, @Value10)";
            using (SqlCommand command = new SqlCommand(query, connection))
            {

                command.Parameters.AddWithValue("@Value1", record.A1);
                command.Parameters.AddWithValue("@Value2", record.A2);
                command.Parameters.AddWithValue("@Value3", record.A3 ?? (object)DBNull.Value);
                command.Parameters.AddWithValue("@Value4", record.A4);
                command.Parameters.AddWithValue("@Value5", record.A5 ?? (object)DBNull.Value);
                command.Parameters.AddWithValue("@Value6", record.A6 ?? (object)DBNull.Value);
                command.Parameters.AddWithValue("@Value7", record.A7 ?? (object)DBNull.Value);
                command.Parameters.AddWithValue("@Value8", record.A8);
                command.Parameters.AddWithValue("@Value9", record.A9);
                command.Parameters.AddWithValue("@Value10", record.A10);


                command.ExecuteNonQuery();
            }
        }
    }
}

}