Write custom source with Script Component: I can give you a sample.
Add connection to your text/csv file and name it as MyFlatFile
So in your script component, Add the following things:
1. Make the script compenent as source when drop it on data flow tab
2. Click "Add" in connection managers(inside script component) and select MyFlatFile conn from drop down
4. Go to Input and Output tab.
5. Rename Output0 to My
6. Define all the columns you need by clicking Add Column, you may set Unicode string [DT_WSTR] for string columns and specify max length as 100, just common figure 100
7. Edit script and replace all code with the following(do remember to make appropriate changes on the columns you defined on step 6):
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.IO;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
private StreamReader textReader;
private string SourceFeed;
public override void AcquireConnections(object Transaction)
{
IDTSConnectionManager100 connMgr = this.Connections.MyFlatFile;
SourceFeed = (string)connMgr.AcquireConnection(null);
}
public override void PreExecute()
{
base.PreExecute();
textReader = new StreamReader(SourceFeed);
}
public override void CreateNewOutputRows()
{
string nextLine;
string[] columns;
char[] delimiters;
delimiters = "|".ToCharArray();
nextLine = textReader.ReadLine();
while (nextLine != null)
{
columns = nextLine.Split(delimiters);
if(MyBuffer.Length > 3)
{
MyBuffer.AddRow();
MyBuffer.Type = columns[0].Trim();
if (columns[0]=="N")
{
MyBuffer.Designation = columns[1].Trim();
MyBuffer.Date = columns[2].Trim();
MyBuffer.Gender = columns[3].Trim();
}
if (columns[0]=="P")
{
MyBuffer.Name = columns[1].Trim();
MyBuffer.CreateDate = columns[2].Trim();
MyBuffer.ModifiedDate = columns[3].Trim();
}
if (columns[0]=="D")
{
MyBuffer.Name = columns[1].Trim();
MyBuffer.SecondField = columns[2].Trim();
MyBuffer.ThirdField = columns[3].Trim();
}
nextLine = textReader.ReadLine();
}
}
public override void PostExecute()
{
base.PostExecute();
textReader.Close();
}
}