I have created a transformation to copy data from CSV file to SQL Server table. Since my CSV file is dynamic and can have any number of columns at given time, I included a ScriptComponent Task to maintain the mapping b/w 'FlatFileSource' and 'OLEDBDestination' task.
I am able to copy data now but the column names row is also getting added as a data row in destination table which I don't want. Below is the code I have written in script task to read csv and add rows to output buffer.
/* Microsoft SQL Server Integration Services Script Component
* Write scripts using Microsoft Visual C# 2008.
* ScriptMain is the entry point class of the script.*/
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.IO;
using System.Text.RegularExpressions;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
private StreamReader sr = null;
public override void PreExecute()
{
base.PreExecute();
/*
Add your code here for preprocessing or remove if not needed
*/
string sourceFile = Variables.FilePath;
if (File.Exists(sourceFile))
{
try
{
sr = new StreamReader(sourceFile);
}
catch
{
}
}
}
public override void PostExecute()
{
base.PostExecute();
/*
Add your code here for postprocessing or remove if not needed
You can set read/write variables here, for example:
Variables.MyIntVar = 100
*/
}
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
/*
Add your code here
*/
}
public override void CreateNewOutputRows()
{
/*
Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".
*/
if (sr != null)
{
while (!sr.EndOfStream)
{
string headline = sr.ReadLine();
if (!string.IsNullOrEmpty(headline))
{
//get columns value by comma as delimited and Double Quotation as text qualifier
//char[] param = {',', @'\'};
string[] columns = headline.Split(',');
Output0Buffer.AddRow();
Output0Buffer.Source = Variables.Source.ToString();
Output0Buffer.Column = columns.Length > 0 ? columns[0].ToString() : null;
Output0Buffer.Column1 = columns.Length > 0 ? columns[1].ToString() : null;
Output0Buffer.Column2 = columns.Length > 0 ? columns[2].ToString() : null;
Output0Buffer.Column3 = columns.Length > 0 ? columns[3].ToString() : null;
Output0Buffer.Column4 = columns.Length > 0 ? columns[4].ToString() : null;
Output0Buffer.Column5 = columns.Length > 0 ? columns[5].ToString() : null;
Output0Buffer.Column6 = columns.Length > 0 ? columns[6].ToString() : null;
Output0Buffer.Column7 = columns.Length > 0 ? columns[7].ToString() : null;
Output0Buffer.Column8 = columns.Length > 0 ? columns[8].ToString() : null;
Output0Buffer.Column9 = columns.Length > 0 ? columns[9].ToString() : null;
Output0Buffer.Column10 = columns.Length > 0 ? columns[10].ToString() : null;
Output0Buffer.Column11 = columns.Length > 0 ? columns[11].ToString() : null;
Output0Buffer.Column12 = columns.Length > 0 ? columns[12].ToString() : null;
Output0Buffer.Column13 = columns.Length > 0 ? columns[13].ToString() : null;
Output0Buffer.Column14 = columns.Length > 0 ? columns[14].ToString() : null;
Output0Buffer.Column15 = columns.Length > 0 ? columns[15].ToString() : null;
Output0Buffer.Column16 = columns.Length > 0 ? columns[16].ToString() : null;
Output0Buffer.Column17 = columns.Length > 0 ? columns[17].ToString() : null;
Output0Buffer.Column18 = columns.Length > 0 ? columns[18].ToString() : null;
Output0Buffer.Column19 = columns.Length > 0 ? columns[19].ToString() : null;
Output0Buffer.Column20 = columns.Length > 0 ? columns[20].ToString() : null;
Output0Buffer.Column21 = columns.Length > 0 ? columns[21].ToString() : null;
Output0Buffer.Column22 = columns.Length > 0 ? columns[22].ToString() : null;
Output0Buffer.Column23 = columns.Length > 0 ? columns[23].ToString() : null;
Output0Buffer.Column24 = columns.Length > 0 ? columns[24].ToString() : null;
Output0Buffer.Column25 = columns.Length > 0 ? columns[25].ToString() : null;
Output0Buffer.Column26 = columns.Length > 0 ? columns[26].ToString() : null;
Output0Buffer.Column27 = columns.Length > 0 ? columns[27].ToString() : null;
Output0Buffer.Column28 = columns.Length > 0 ? columns[28].ToString() : null;
Output0Buffer.Column29 = columns.Length > 0 ? columns[29].ToString() : null;
Output0Buffer.Column30 = columns.Length > 0 ? columns[30].ToString() : null;
Output0Buffer.Column31 = columns.Length > 0 ? columns[31].ToString() : null;
Output0Buffer.Column32 = columns.Length > 0 ? columns[32].ToString() : null;
Output0Buffer.Column33 = columns.Length > 0 ? columns[33].ToString() : null;
Output0Buffer.Column34 = columns.Length > 0 ? columns[34].ToString() : null;
Output0Buffer.Column35 = columns.Length > 0 ? columns[35].ToString() : null;
Output0Buffer.Column36 = columns.Length > 0 ? columns[36].ToString() : null;
Output0Buffer.Column37 = columns.Length > 0 ? columns[37].ToString() : null;
Output0Buffer.Column38 = columns.Length > 0 ? columns[38].ToString() : null;
Output0Buffer.Column39 = columns.Length > 0 ? columns[39].ToString() : null;
Output0Buffer.Column40 = columns.Length > 0 ? columns[40].ToString() : null;
Output0Buffer.Column41 = columns.Length > 0 ? columns[41].ToString() : null;
Output0Buffer.Column42 = columns.Length > 0 ? columns[42].ToString() : null;
Output0Buffer.Column43 = columns.Length > 0 ? columns[43].ToString() : null;
Output0Buffer.Column44 = columns.Length > 0 ? columns[44].ToString() : null;
Output0Buffer.Column45 = columns.Length > 0 ? columns[45].ToString() : null;
Output0Buffer.Column46 = columns.Length > 0 ? columns[46].ToString() : null;
Output0Buffer.Column47 = columns.Length > 0 ? columns[47].ToString() : null;
Output0Buffer.Column48 = columns.Length > 0 ? columns[48].ToString() : null;
Output0Buffer.Column49 = columns.Length > 0 ? columns[49].ToString() : null;
}
}
}
}
}
Please let me know if i need to put any more info from my ssis here. Thanks!!
