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; try { if (!File.Exists(FILE_NAME)) { MessageBox.Show("File " + FILE_NAME + " does not exist."); ReturnValue = (int)ScriptResults.Failure; return; } using (StreamReader sr = File.OpenText(FILE_NAME)) { while ((line = sr.ReadLine()) != null) { //MessageBox.Show(line); columns = line.Split(delimiters); //MessageBox.Show(columns[0].ToString()); //MessageBox.Show(columns[1].ToString()); if (columns[0].ToString() == "Number Of Customers") { //MessageBox.Show(columns[0].ToString()); //MessageBox.Show(columns[1].ToString()); Dts.Variables["NoOfCustomers"].Value = Convert.ToInt32(columns[1].ToString()); } } ReturnValue = (int)ScriptResults.Success; } } catch (Exception e) { MessageBox.Show(e.Message); ReturnValue = (int)ScriptResults.Failure; } Dts.TaskResult = ReturnValue; } } } // ref: http://msdn.microsoft.com/en-us/library/db5x7c0d.aspx
[…] 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 |