SSIS column count from a flat file

Question

I'm trying to find a way to count my columns coming from a Flat File. Actually, all my columns are concatened in a signe cell, sepatared with a '|' ,
after various attempts, it seems that only a script task can handle this. Does anyone can help me upon that ? I've shamely no experience with script in C# ou VB.

Thanks a lot Emmanuel

To better understand, below is the output of what I want to achieve to. e.g a single cell containing all headers coming from a FF. The thing is, to get to this result, I appended manually in the previous step ( derived column) all column names each others in order to concatenate them with a '|' separator. Now , if my FF source layout changes, it won't work anymore, because of this manualy process. So I think I would have to use a script instead which basically returns my number of columns (header ) in a variable and will allow to remove the hard coded part in the derived column transfo for instance

Answer 1

This is an very old thread; however, I just stumbled on a similar problem. A flat file with a number of different record "formats" inside. Many different formats, not in any particular order, meaning you might have 57 fields in one line, then 59 in the next 1000, then 56 in the next 10000, back to 57... well, think you got the idea.

For lack of better ideas, I decided to break that file based on the number of commas in each line, and then import the different record types (now bunched together) using SSIS packages for each type.

So the answer for this question is there, with a bit more code to produce the files.

Hope this helps somebody with the same problem.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;

namespace OddFlatFile_Transformation
{
    class RedistributeLines
    {
    /*
     * This routine opens a text file and reads it line by line
     * for each line the number of "," (commas) is counted
     * and then the line is written into a another text file
     * based on that number of commas found
     * For example if there are 15 commas in a given line
     * the line is written to the WhateverFileName_15.Ext
     * WhaeverFileName and Ext are the same file name and 
     * extension from the original file that is being read
     * The application tests WhateverFileName_NN.Ext for existance
     * and creates the file in case it does not exist yet
     * To Better control splited records a sequential identifier, 
     * based on the number of lines read, is added to the beginning
     * of each line written independently of the file and record number
     */
        static void Main(string[] args)
        {
            // get full qualified file name from console
            String strFileToRead;
            strFileToRead = Console.ReadLine();

            // create reader & open file
            StreamReader srTextFileReader = new StreamReader(strFileToRead);

            string strLineRead = "";
            string strFileToWrite = "";
            string strLineIdentifier = "";
            string strLineToWrite = "";
            int intCountLines = 0;
            int intCountCommas = 0;
            int intDotPosition = 0;
            const string strZeroPadding = "00000000";

            // Processing begins
            Console.WriteLine("Processing begins: " + DateTime.Now);

            /* Main Loop */
            while (strLineRead != null)
            {
                // read a line of text count commas and create Linde Identifier
                strLineRead = srTextFileReader.ReadLine();
                if (strLineRead != null)
                {
                    intCountLines += 1;
                    strLineIdentifier = strZeroPadding.Substring(0, strZeroPadding.Length - intCountLines.ToString().Length) + intCountLines;
                    intCountCommas = 0;
                    foreach (char chrEachPosition in strLineRead)
                    {
                        if (chrEachPosition == ',') intCountCommas++;
                    }

                    // Based on the number of commas determined above
                    // the name of the file to be writen to is established
                    intDotPosition = strFileToRead.IndexOf(".");
                    strFileToWrite = strFileToRead.Substring (0,intDotPosition) + "_";
                    if ( intCountCommas < 10)
                    {
                        strFileToWrite += "0" + intCountCommas;
                    }
                    else
                    {
                        strFileToWrite += intCountCommas;
                    }
                    strFileToWrite += strFileToRead.Substring(intDotPosition, (strFileToRead.Length - intDotPosition));

                    // Using the file name established above the line captured
                    // during the text read phase is written to that file

                    StreamWriter swTextFileWriter = new StreamWriter(strFileToWrite, true);
                    strLineToWrite = "[" + strLineIdentifier + "] " + strLineRead; 
                    swTextFileWriter.WriteLine (strLineToWrite);
                    swTextFileWriter.Close();
                     Console.WriteLine(strLineIdentifier);
               }
            }

            // close the stream
            srTextFileReader.Close();
            Console.WriteLine(DateTime.Now);
            Console.ReadLine();
        }
    }


}

Answer 2

Please refer my answers in the following Stack Overflow questions. Those answers might give you an idea of how to load a flat file that contains varying number of columns.

1. Example in the following question reads a file containing data separated by special character Ç (c-cedilla). In your case, the delimiter is Vertical Bar (|) UTF-8 flat file import to SQL Server 2008 not recognizing {LF} row delimiter

2. Example in the following question reads an EDI file that contains different sections with varying number of columns. The package reads the file loads it accordingly with parent-child relationships into an SQL table. how to load a flat file with header and detail parent child relationship into SQL server

Based on the logic used in those answers, you can also count the number of columns by splitting the rows in the file by the column delimiter (Vertical Bar |).

Hope that helps.