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.
Previous video: • SSIS Tutorial - 3 ways to Import Excel to ...
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
// MessageBox.Show(Variables.outData.GetType().ToString());
// 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()
{
// Variables.outData is a System.Data.DataSet
// A DataSet already has a DataTable in it.
DataSet DS = (DataSet)Variables.outData;
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);
}
string filename = folder + $"\\{Variables.strFileName.ToString()}.txt";
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);
}
string filename = folder + $"\\{Variables.strFileName.ToString()}.txt";
OleDbDataAdapter da = new OleDbDataAdapter();
DataTable dt = new DataTable();
da.Fill(dt, Variables.outData);
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
da.Fill(dt, Variables.outData);
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')
On this page of the site you can watch the video online SSIS Tutorial - How to use Script Component dynamically export data to multiple text files. with a duration of online in good quality, which was uploaded by the user 寂寞飲者 • Lonesome Booze 01 January 1970, share the link with friends and acquaintances, this video has already been watched 137 times on youtube and it was liked by 1 viewers. Enjoy your viewing!
