Ggtcf

I have to table in MySQL Server.

1. Header Table.
   
╔════════════╦════════╦═════════════╦═════════════════╦══════════╗
║ RecordType ║ CustID ║ DataGenDate ║ DataCreatedDate ║ SourceID ║
╠════════════╬════════╬═════════════╬═════════════════╬══════════╣
║ H ║ #1234 ║ 2018-01-05 ║ 2018-01-01 ║ V301 ║
╚════════════╩════════╩═════════════╩═════════════════╩══════════╝




2. Transaction Table
   
╔════════════╦══════════╦══════════════╦══════════════╦════════════╗
║ RecordType ║ ProdCode ║ OpeningValue ║ ClosingValue ║ TranDate ║
╠════════════╬══════════╬══════════════╬══════════════╬════════════╣
║ T ║ AL001 ║ 95 ║ 90 ║ 2018-01-01 ║
╠════════════╬══════════╬══════════════╬══════════════╬════════════╣
║ T ║ AL002 ║ 54 ║ 40 ║ 2018-01-01 ║
╠════════════╬══════════╬══════════════╬══════════════╬════════════╣
║ T ║ AL003 ║ 63 ║ 43 ║ 2018-01-02 ║
╠════════════╬══════════╬══════════════╬══════════════╬════════════╣
║ T ║ AL004 ║ 56 ║ 23 ║ 2018-01-01 ║
╚════════════╩══════════╩══════════════╩══════════════╩════════════╝

Header Table has Header Information and Transaction table have Transaction Data.
I want a text file (vertical pipe separated "|") to be generated through SSIS in below format.

H|#1234|2018-01-05|2018-01-01|V301
----------------------------------------
T|AL001|95 |90 |2018-01-01
T|AL002|54 |40 |2018-01-01
T|AL003|63 |43 |2018-01-02
T|AL004|56 |23 |2018-01-01

I tried it with ole DB source and flat file destination to export the file but wasn't successful. only I am getting either transaction or Header Rows.

This can be done using a Script Task as follows, with C# used in this case. This will create a CSV file with the pipe (|) delimiter. Running a sample test with this I was able to import the output CSV file via an SSIS Flat File Connection Manager without any modifications to the file. This example assumes there's only a single row in the header table, otherwise you'll need to modify the SQL for this table to return the proper row.

using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;



//Windows Authentication (Integrated Security)
 string connectionString = @"Data Source=ServerName;Initial Catalog=DatabaseName;Integrated Security=true";
 string headerCmd = @"SELECT RecordType, CustID, DataGenDate, DataCreatedDate, SourceID FROM HeaderTable";
 string rowCmd = @"SELECT RecordType, CustID, DataGenDate, DataCreatedDate, SourceID FROM TransactionTable";

 string outputFile = Dts.Variables["User::FilePathVariable"].Value.ToString();

 StringBuilder csvData = new StringBuilder();
 int headerInt = 0;
 DataTable headerDT = new DataTable();
 DataTable rowDT = new DataTable();

 using (SqlConnection conn = new SqlConnection(connectionString))
 
 SqlCommand headerSQL = new SqlCommand(headerCmd, conn);
 SqlCommand rowSQL = new SqlCommand(rowCmd, conn);

 SqlDataAdapter da = new SqlDataAdapter();

 conn.Open();

 //get header row
 da.SelectCommand = headerSQL;
 da.Fill(headerDT);

 //get data from Transaction table
 da.SelectCommand = rowSQL;
 da.Fill(rowDT);
 

 //build header
 foreach (DataRow hDR in headerDT.Rows)
 
 foreach (DataColumn hDC in headerDT.Columns)
 
 csvData.Append(hDR[headerInt].ToString() + "
 

 //remove last pipe then start new line 
 csvData.Remove(csvData.Length - 1, 1);
 csvData.Append(Environment.NewLine);

 //add rows
 foreach (DataRow rDR in rowDT.Rows)
 
 for (int i = 0; i < headerInt; i++)
 
 csvData.Append(rDR[i] + "
 csvData.Remove(csvData.Length - 1, 1);
 csvData.Append(Environment.NewLine);
 
 //write to CSV
 File.WriteAllText(outputFile, csvData.ToString());