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')
In questa pagina del sito puoi guardare il video online SSIS Tutorial - How to use Script Component dynamically export data to multiple text files. della durata di ore minuti seconda in buona qualità , che l'utente ha caricato 寂寞飲者 • Lonesome Booze 01 gennaio 1970, condividi il link con amici e conoscenti, su youtube questo video è già stato visto 118 volte e gli è piaciuto 1 spettatori. Buona visione!
