CODEWITHSUNDEEP
c#vb.netvisual-studiossis
DanteeChaos
DanteeChaos

Reputation: 333

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

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.

Upvotes: 1

Views: 2204

Answers (3)

TheEsnSiavashi
TheEsnSiavashi

Reputation: 1255

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:

enter image description here

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.

Upvotes: 2

KeithL
KeithL

Reputation: 5594

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

Add a Dataflow Task

In dataflow add these objects:

DF

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.

Upvotes: 0

Michal S
Michal S

Reputation: 481

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
}

Upvotes: 1

Related Questions