Data Warehousing and Business Intelligence

21 June 2011

Updating a Variable based on a File (Script Task)

Filed under: SSIS — Vincent Rainardi @ 10:24 pm

In the last article (link) I showed how to update a variable based on a file for script component. That’s useful when we are in a Work Flow. But sometimes the situation happens on the Control Flow. In this case we need to do a Script Task.

Using a Script Task it is a lot simpler than using a Script Component. We essentially need to do a bit of C# coding to read the file and set the variable.

This is the file that we want to read:

Pull the Script Task from the Toolbar to the Control Flow tab:

Define a variable to hold the Number of Customer:

Double click the Script Task to edit it. Set the read write variable to the variable we just created (NoOfCustomers):

Double click the Edit Script and edit it as follows:

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;

  public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    public void Main()
      const string FILE_NAME = "C:\\Util\\File.txt";
      int ReturnValue;
      String line;
      char[] delimiters = "|".ToCharArray();
      string[] columns;
        if (!File.Exists(FILE_NAME))
          MessageBox.Show("File " + FILE_NAME + " does not exist.");
          ReturnValue = (int)ScriptResults.Failure;
        using (StreamReader sr = File.OpenText(FILE_NAME))
          while ((line = sr.ReadLine()) != null)
            columns = line.Split(delimiters);

            if (columns[0].ToString() == "Number Of Customers")
              Dts.Variables["NoOfCustomers"].Value = Convert.ToInt32(columns[1].ToString());
          ReturnValue = (int)ScriptResults.Success;

      catch (Exception e)
        ReturnValue = (int)ScriptResults.Failure;

      Dts.TaskResult = ReturnValue;

// ref:


1 Comment »

  1. […] set the value of a variable from a file Link1, Link2 […]

    Pingback by Coming SSIS Articles « Data Warehousing and Business Intelligence — 21 June 2011 @ 10:37 pm | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at

%d bloggers like this: