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')
Auf dieser Seite können Sie das Online-Video SSIS Tutorial - How to use Script Component dynamically export data to multiple text files. mit der Dauer stunde minuten sekunde in guter Qualität ansehen, das der Benutzer 寂寞飲者 • Lonesome Booze 01 Januar 1970 hochgeladen hat, den Link mit Freunden und Bekannten teilen, dieses Video wurde auf Youtube bereits 118 Mal angesehen und es wurde von 1 den Zuschauern gefallen. Viel Spaß beim Betrachtenden Zuschauern gefallen!
