How to pass a dynamically changing string to a script task?

Question

I have a SSIS solution and package. The package includes a Script task that sends a message via API call to Telegram. So far this works ok as the package receives two variables: API token and ChatId. Inside the package I have temporarily defined a message to send.

My concern here is that I would like to use the same script to send different messages depending on the conditions that occur during package execution and I dont want to have the same script task with a different message inside it. I have been trying to consider package variables but dont really understand how to use them to send variable messages as each script task will require a different variable

This is my control flow so far. As you can see, the script task runs multiple times depending on the task context, requiring different messages to be sent.

This is the main part of my script task. As you can see I have temporarily set a fixed message and thats the one I would like to be dynamic (think of it as a function parameter)

public void Main()
{

    ///TELEGRAM API
    ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;
    string urlString = "https://api.telegram.org/bot{0}/sendMessage?chat_id={1}&text={2}&parse_mode=html";
    string apiToken = (String)Dts.Variables["User::TelegramToken"].Value;
    string chatId = (String)Dts.Variables["User::TelegramChatId"].Value;
    string text = "This is the message to be sent";
    urlString = String.Format(urlString, apiToken, chatId, text); 
    WebRequest request = WebRequest.Create(urlString); 
    Stream rs = request.GetResponse().GetResponseStream(); 
    StreamReader reader = new StreamReader(rs); 
    string line = ""; 
    StringBuilder sb = new StringBuilder();
    while (line != null)
    {
        line = reader.ReadLine();
        if (line != null)
            sb.Append(line);
    }
    string response = sb.ToString();
   ///TELEGRAM API

    Dts.TaskResult = (int)ScriptResults.Success;
}

EDIT1:I was just thinking about using en Expression task to set a variable before executing the script. Would that be an appropriate alternative?

Mark Wojciechowicz · Accepted Answer · 2022-07-18 17:56:24Z

2

Yes, use an expression task to set the message. These are the changes that I recommend:

Put everything before the telegram message into a sequence container and connect that sequence container to the telegram script task
Add a string variable at the package level to hold the message
Set the message variable with expressions within the sequence container above
Replace the hard-coded text with the variable reference

...
    string text = (String)Dts.Variables["User::Message"].Value;
...

answered Jul 18, 2022 at 17:56

Mark Wojciechowicz

4,4871 gold badge20 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to pass a dynamically changing string to a script task?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related