Home > database >  How to pass a dynamically changing string to a script task?
How to pass a dynamically changing string to a script task?

Time:07-19

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. enter image description here

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?

CodePudding user response:

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

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

enter image description here

enter image description here

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