Reputation: 641
I have to table in MySQL Server.
Header Table.
╔════════════╦════════╦═════════════╦═════════════════╦══════════╗
║ RecordType ║ CustID ║ DataGenDate ║ DataCreatedDate ║ SourceID ║
╠════════════╬════════╬═════════════╬═════════════════╬══════════╣
║ H ║ #1234 ║ 2018-01-05 ║ 2018-01-01 ║ V301 ║
╚════════════╩════════╩═════════════╩═════════════════╩══════════╝
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.
Upvotes: 3
Views: 143
Reputation: 4810
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() + "|");
headerInt++;
}
}
//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());
Upvotes: 3