Problem: Suppose we have a folder where the flat-file downloaded from any organization. Now we have to transfer data from flat
file (comma separated file) to our database’s table but top n lines are the descriptions
about the file, this thing may create a problem to access data from flat file to
Table. File looks
like below
Solution: If we remove the top n description line
from the flat file, then we can efficiently process data from flat file to the database
table.
Steps -1: Hope you are aware of Flow
Control and Data Flow
Step -2: Select tool Script Component
Step -3: Create a variable for file path and
the number of lines to delete.
Step-4: Edit Script--> Custom
Property--> Read Write Variable and add both users-defined variables.
Step -5: Click on the button Edit Script. A
script file will be open. Add following namespace
using
System.IO;
using
System. Linq;
Step-6: Write down the following code.
public override void
PreExecute()
{
base.PreExecute();
}
/// <summary>
/// This method is called after all the rows have passed
through this component.
///
/// You can delete this method if you don't need to do
anything here.
/// </summary>
public
override void
PostExecute()
{
string fPath = Variables.fPath;
int deleteLines = Variables.lineNumber;
string[] lines = File.ReadAllLines(fPath);
lines =
lines.Skip(deleteLines).ToArray();
using (StreamWriter
sr = new StreamWriter(fPath))
{
foreach (var v in lines)
{
sr.WriteLine(v);
}
}
base.PostExecute();
/*
* Add
your code here
*/
}
public
override void
CreateNewOutputRows()
{
Output0Buffer.AddRow();
Output0Buffer.MyColumn = 10;
}
|
Step-7:
After saving script run the package, you find the expected result.
Note: Above
Script override the existing file. This tutorial is written for SSIS 2012.