How to look through a text file for a specific string and paste that string into a variable in SSIS/C#/VB

Question

I'm rewriting an old piece of software into a SSIS package. I have a text file which looks similar to this:

junk
other junk
other junk2
Sent: Friday, July 14, 2017 1:56 PM
more junk
more junk2

I need to open the file (path and filename are constants), look for "Sent:" and copy everything untill the new line (which in this case is " Friday, July 14, 2017 1:56 PM") into a variable that can be used by the SSIS package later on. My understanding is that SSIS doesn't have the required components and that I'll need a script task to do this. Could anyone help me out with this, please?

Also, since I have no experience using the script task/C#/VB a detailed guide would really be appreciated.

TheEsnSiavashi · Accepted Answer · 2017-07-19 15:11:26Z

2

You need a Script Component not a Script Task because you are going to grab the data using a Flat File Connection in Data Flow level. In your Flat File Source have only one column and call it Column0.

Then add a Transformation type of Script Component. Go to Input Columns tab first and select the Column0. Then, you need to go back to the Script tap and add the output variable, OutputLine, as a read and write variable like this:

Click on the Edit Script button and there are 3 methods: PreExecute, PostExecute and Input0_ProcessInputRow. Add the following lines of code to PostExecute and Input0_ProcessInputRow:

public override void PostExecute()
{
    base.PostExecute();
    this.Variables.OutputLine = LineValue;
}

// Add this variable that will hold the desired line value.
string LineValue = "";

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    if (Row.Column0.StartsWith("Sent:"))
    {
        LineValue = Row.Column0.Substring(5);
    }

}

Build it, close it, click on OK and you are done.

edited Jul 19, 2017 at 15:11

answered Jul 18, 2017 at 15:24

TheEsnSiavashi

1,2551 gold badge15 silver badges31 bronze badges

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

2 Comments

DanteeChaos Over a year ago

Thanks for helping. unfortunately I'm getting an error whenever I try to run the package "The collection of variables locked for read and write access is not available outside of PostExecute." Error screen -imgur.com/a/iavwB. Any Ideas?

TheEsnSiavashi Over a year ago

@DanteeChaos Oops! I updated the solution. Define a string value that holds the desired line value and then assign the value of that variable to the OutputLine variable in PostExecute.

Michal S · Accepted Answer · 2017-07-18 14:35:27Z

1

I have no idea about SSIS or how to use C# in there, but here's a really basic C# method in case it helps you somehow:

public string GetSent()
{
    foreach (var line in File.ReadLines("sample.txt")) // replace with your path
    {
        if (line.StartsWith("Sent:"))
        {
            return line.Substring(5);
        }
    }

    return "NOT FOUND"; // or null or whatever you want to default to
}

answered Jul 18, 2017 at 14:35

Michal S

4711 gold badge4 silver badges8 bronze badges

Comments

KeithL · Accepted Answer · 2017-07-19 13:24:12Z

0

Create a package with an ADO object variable and a string variable.

Add a Dataflow Task

In dataflow add these objects:

Create connection to flat file

In Condition split set up an output where left(col1,5) == "Sent:"

Connect conditional split with sent output to Derived Column

Derive a column and use substring

map record set to object variable and map to derived column

Back in Control Flow use the object in Foreach using an ADO Enumerator and map the column to the string variable.

edited Jul 19, 2017 at 13:24

answered Jul 18, 2017 at 15:02

KeithL

5,6193 gold badges23 silver badges30 bronze badges

1 Comment

DanteeChaos Over a year ago

To be honest, I got lost right after the condition split. Could you please explain the remaining steps (starting with "Connect Derived Column to Derived Column") in more detail? I would appreciate additional screenshots. I tired to follow you to the best of my ability but somehow I'm ending up with an "User::ADOvar does not contain a valid data object" error during the validation phase. I probably screwed up somwhere during the Derived column or Recordset Destination phase.

Collectives™ on Stack Overflow

How to look through a text file for a specific string and paste that string into a variable in SSIS/C#/VB

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related