Assuming you have TextFile connection on your Package.
Add a script component - Source.
Inside Script Compoenent Editor: In the connection manager pane, select your text file connection, and name it as "SourceFeedTextConn"
Create Required Outputs in Input and Output pane. say MyType1Output, MyType2Output , add required columns to each output
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.SourceFeedTextConn;
SourceFeed = (string)connMgr.AcquireConnection(null);
}
public override void PreExecute()
{
base.PreExecute();
textReader = new StreamReader(SourceFeed);
}
public override void CreateNewOutputRows()
{
string nextLine;
string[] columns;
string type="";
char[] delimiters;
delimiters = ";".ToCharArray();
nextLine = textReader.ReadLine();
columns = nextLine.Split(delimiters);
string header = string.Join(";;", columns);
if (header.IndexOf("MyType1Col1", StringComparison.InvariantCultureIgnoreCase) > -1
&& header.IndexOf("MyType1Col2", StringComparison.InvariantCultureIgnoreCase)> -1
&& header.IndexOf("MyType1Col3", StringComparison.InvariantCultureIgnoreCase) > -1)
type="MyType1";
if (header.IndexOf("MyType2Col1", StringComparison.InvariantCultureIgnoreCase) > -1
&& header.IndexOf("MyType2Col2", StringComparison.InvariantCultureIgnoreCase) > -1
&& header.IndexOf("MyType2Col3", StringComparison.InvariantCultureIgnoreCase) > -1)
type="MyType2";
nextLine = textReader.ReadLine();
while (nextLine != null)
{
columns = nextLine.Split(delimiters);
if(type=="MyType1")
{
MyType1Output.AddRow();
MyType1Output.MyType1Col1 = columns[0].Replace("\"", "").Replace(";", "").Trim();
MyType1Output.MyType1Col2 = columns[1].Replace("\"", "").Replace(";", "").Trim();
}
if(type=="MyType2")
{
MyType2Output.AddRow();
MyType2Output.MyType2Col1 = columns[0].Replace("\"", "").Replace(";", "").Trim();
MyType2Output.MyType2Col2 = columns[1].Replace("\"", "").Replace(";", "").Trim();
}
nextLine = textReader.ReadLine();
}
}
public override void PostExecute()
{
base.PostExecute();
textReader.Close();
}
}
Now Add add destination components and connect each output of Script component to each destination.
Let me know if you need any help.
Thanks,
Kuthuparakkal