SSIS Tutorial - How to use Script Component dynamically export data to multiple text files.

Visual Studio 2019 SSIS Tutorial - How to use Script Component dynamically export data to multiple text files.

Script example:

#region Namespaces
...
// add
using System.Windows.Forms;
using System.Data.OleDb;
using System.IO;
#endregion
...
public override void PreExecute()
{
base.PreExecute();
// Uncomment next line to check data type of outData

// when using Ole DB connection, outData is _ComObject type
// when using ADO.NET connection, outData is DataSet type
}
...

/* Good for ADO.NET connection data & when outData is System.Data.DataSet */
public override void CreateNewOutputRows()
{
// A DataSet already has a DataTable in it.
DataTable dt = DS.Tables[0]; // The first Table

string userNmae = Variables.UserName.Substring(6, 8);
string myData = "";

string folder = $"C:\\Users\\{userNmae}\\{Variables.ResultFolder}";
if (!Directory.Exists(folder))
{ // Create the folder if it doesn't exist
Directory.CreateDirectory(folder);
}

foreach (DataRow dr in dt.Rows)
{
myData += dr["TermDescr"].ToString() + Environment.NewLine;
}
//Export to text file by using StreamWriter
using (StreamWriter writer = System.IO.File.CreateText(filename))
{
// Write the data
writer.Write(myData);
}
// using() will close the writer automatically
}

/* // Good for OLEDB connection data & when outData is System._ComObject type
public override void CreateNewOutputRows()
{
string userNmae = Variables.UserName.Substring(6, 8);
string myData = "";

string folder = $"C:\\Users\\{userNmae}\\{Variables.ResultFolder}";
if (!Directory.Exists(folder))
{ // Create the folder if it doesn't exist
Directory.CreateDirectory(folder);
}

OleDbDataAdapter da = new OleDbDataAdapter();
DataTable dt = new DataTable();

foreach (DataRow dr in dt.Rows)
{
myData += dr["TermDescr"].ToString() + Environment.NewLine;
}
//Export to text file by using StreamWriter
using (StreamWriter writer = System.IO.File.CreateText(filename))
{
// Write the data
writer.Write(myData);
}
}
*/

/* // Good for OLEBD connection, one file export & using Flat File Destination
public override void CreateNewOutputRows()
{
OleDbDataAdapter da = new OleDbDataAdapter();
DataTable dt = new DataTable();
// Extract the data from the user variable into the table

foreach (DataRow dr in dt.Rows)
{
// Create a new, empty row in the output buffer
MyOutputBuffer.AddRow();
MyOutputBuffer.TermDescr = dr["TermDescr"].ToString();
}
}
*/

SQL code of Data Table

Create Table TermTbl (
TermDescr Varchar(20) Not Null,
CalYear Varchar(4) Not Null
);

Insert Into TermTbl (TermDescr, CalYear) Values('Spring/Summer 2021', '2021')
Insert Into TermTbl (TermDescr, CalYear) Values('Spring/Summer 2022', '2022')
Insert Into TermTbl (TermDescr, CalYear) Values('Spring/Summer 2023', '2023')
Insert Into TermTbl (TermDescr, CalYear) Values('Spring/Summer 2024', '2024')
Insert Into TermTbl (TermDescr, CalYear) Values('Summer 2021', '2021')
Insert Into TermTbl (TermDescr, CalYear) Values('Summer 2022', '2022')
Insert Into TermTbl (TermDescr, CalYear) Values('Summer 2023', '2023')
Insert Into TermTbl (TermDescr, CalYear) Values('Summer 2024', '2024')
Insert Into TermTbl (TermDescr, CalYear) Values('Winter 2021', '2021')
Insert Into TermTbl (TermDescr, CalYear) Values('Winter 2022', '2022')
Insert Into TermTbl (TermDescr, CalYear) Values('Winter 2023', '2023')
Insert Into TermTbl (TermDescr, CalYear) Values('Winter 2024', '2024')
Insert Into TermTbl (TermDescr, CalYear) Values('Spring 2021', '2021')
Insert Into TermTbl (TermDescr, CalYear) Values('Spring 2022', '2022')
Insert Into TermTbl (TermDescr, CalYear) Values('Spring 2023', '2023')
Insert Into TermTbl (TermDescr, CalYear) Values('Spring 2024', '2024')
Insert Into TermTbl (TermDescr, CalYear) Values('Fall 2021', '2021')
Insert Into TermTbl (TermDescr, CalYear) Values('Fall 2022', '2022')
Insert Into TermTbl (TermDescr, CalYear) Values('Fall 2023', '2023')
Insert Into TermTbl (TermDescr, CalYear) Values('Fall 2024', '2024')