Reputation: 333
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
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:
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
Reputation: 5594
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.
Upvotes: 0
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