SSIS - Various number of columns to output to flat file

Question

I am currently creating an SSIS that will gather data from database and output it to a single Comma delimited Flat File. The file will contain order details Format of file is

Order#1 details (51 columns)
Order#1 header  (62 columns)
Order#2 details (51 columns)
Order#2 header  (62 columns)
etc...

Order header has 62 columns, order details has 51 columns. I need to output this to a flat file and I am running into an issue because SSIS does not handle varying columns. Can someone please help me and given that my source is an OLEDB source with the query, how do I create a script component to output to a file.

Current Package looks like the following:

1. Get a list of all order. Pass orderid as a variable.
2. For loop container goes through each orderid, runs a data task flow to get the order details for the order. Run a data task to get order header. I am just running into an issue to output each line to Flat file.

IF anyone can help that will be immensely appreciated. I have been struggling with this for a week now.If anyone can start me off with what the script component code should look like that would be immensely appreciated.

I have added what I have so far: http://imgur.com/a/yTxfH

This is what my script looks like:

public void Main()
    {
        // TODO: Add your code here
        DataTable RecordType300 = new DataTable();
        DataTable RecordType210 = new DataTable();
        DataTable RecordType220 = new DataTable();
        DataTable RecordType200 = new DataTable();

        OleDbDataAdapter adapter = new OleDbDataAdapter();
        adapter.Fill(RecordType300, Dts.Variables["User:rec_type300"].Value);
        adapter.Fill(RecordType210, Dts.Variables["User::rec_type_210"].Value);
        adapter.Fill(RecordType220, Dts.Variables["User::rec_type_220"].Value);
        adapter.Fill(RecordType200, Dts.Variables["User::rec_type200"].Value);
        using (StreamWriter outfile = new StreamWriter("C:\\myoutput.csv"))
        {
            for (var i = 0; i < RecordType300.Rows.Count; i++)
            {
                var detailFields = RecordType300.Rows[i].ItemArray.Select(field => field.ToString()).ToArray();
               // var poBillFields = RecordType210.Rows[i].ItemArray.Select(field => field.ToString()).ToArray();
              //  var poShipFields = RecordType220.Rows[i].ItemArray.Select(field => field.ToString()).ToArray();
             //   var poHeaderFields = RecordType200.Rows[i].ItemArray.Select(field => field.ToString()).ToArray();
                outfile.WriteLine(String.Join(",", detailFields));
              //  outfile.WriteLine(string.Join(",", poBillFields));
              //  outfile.WriteLine(string.Join(",", poShipFields));
              //  outfile.WriteLine(string.Join(",", poHeaderFields));
            }
        }

        Dts.TaskResult = (int)ScriptResults.Success;

    }

But every time I run it, it errors out. Am I missing something here? Also, how would I create a file in the beginning only 1 time. Meaning every time this package is run it will create a file with the datestamp and append to it each time. The next time the package runs, it will create a new file with new date stamp and append each order details based on the order number.

Answer 1

This code/method has not been tested but should give you a good idea of what to do.

1. Create 2 SSIS variables of type object, one for the headers and one for the detail.
2. Create 2 Execute SQL tasks and 1 Script Task as outlined here:
3. Setup your Tasks to handle a full result set, similar to these pics (the Detail version is shown, do similar for Header but map results to the Header object and change your query to point at the header table):
4. Edit your script task and allow Detail and Header as read only vars:
5. Edit your actual script now along these lines (this is assuming you have exactly 1 detail row for 1 header row):

using System.IO;
using System.Linq;
using System.Data.OleDb;

// following to be inserted into Main() function
DataTable detailData = new DataTable();
DataTable headerData = new DataTable();
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.Fill(detailData, Dts.Variables["User::Detail"].Value);
adapter.Fill(headerData, Dts.Variables["User::Header"].Value);

using (StreamWriter outfile = new StreamWriter("myoutput.csv"))
{
    // we are making the assumption that 
    for (var i = 0; i < detailData.Rows.Count; i++)
    {
        var detailFields = detailData.Rows[i].ItemArray.Select(field => field.ToString()).ToArray();
        var headerFields = headerData.Rows[i].ItemArray.Select(field => field.ToString()).ToArray();
        outfile.WriteLine(string.Join(",", detailFields));
        outfile.WriteLine(string.Join(",", headerFields));
    }
}

Answer 2

Not a complete answer, just something to put you on the track of an alternative approach

SELECT Type, OrderBy, Col
FROM
(
   SELECT 'D' As Type, Ord as OrderBy, 
   Col1 + ',' + CAST(Col2 AS VARCHAR(50)) + ',' + Col3 As Col
   FROM Details
   UNION ALL
   SELECT 'H' As Type, Ord as OrderBy,
   Col1 + ',' + CAST(Col2 AS VARCHAR(50)) + ',' + Col3 As Col + ',' + Col4
   FROM Header
) S
ORDER BY OrderBy, Type

Its ugly but it works as long as you cast all datatypes to varchar

You can wrap this up in a view or a stored procedure and test it from the database (before you get to the SSIS part). You can even export this using BCP.EXE rather than SSIS

What you have here is one column which happens to contain this kind of data:

 A,B,C
 D,E,F,G

From a metadata perspective there is consistently one column

From a CSV perspective there are variable columns