How to get Column count from flat file

Question

Hi,

Package wants to read the data from flat file and load into SQL tables. the column count needs to be checked from the flat file and load into another table. Is there a way to calculate column count using script component in Data flow? please provide me the piece of vb code part or other ways to get column count.

Any help  is appreciated.

Thanks

---

Dhinesh G MCTS-MSBI Specialist

Answer 1

If there is a separator then you split one row and count the columns

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Answer 2

Column count as opposed to row count?

Does the file have a set number of columns that are not always used or does the file have a dynamic set of columns?

If the former or the later with known max # of columns, I'd just load the file into a SQL Server staging table and use T-SQL to count the used columns there. But then, Script Tasks and I don't always get along. @=)

The other option is to count the number of instances of the column delimiter and add one (for the last column). A pattern matching bit of code.

---

MCITP:DBA, MCDBA, MCSA

Answer 3

Dhinesh :

May be this vb code helps  in your script component provided the column count is fixed

    Sub Main()
        'Method 1
        'sample file c:\test\books.txt
        'id, author, title, genre, price, publish_Date, desc
        'bk101,Gambardella,XML Developer's Guide,Computer,44.95,2000-10-01,An in-depth look at creating applications
        'bk102,Ralls,Midnight Rain,Fantasy,95,2000-12-16,A former architect battles corporate zombies
        Dim objConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\test\\;Extended Properties=text;"
        Dim objTable As New DataTable()
        Using conn As New OleDbConnection(objConn)
            conn.Open()
            Dim command As New OleDbCommand("SELECT * from books.txt", conn)
            Dim adapter As New OleDbDataAdapter(command)
            adapter.Fill(objTable)
            conn.Close()
        End Using
        Console.WriteLine(objTable.Columns.Count)


        'Method2
        Dim fileName As String = "c:\\test\\books.txt"

        If (File.Exists(fileName)) Then
            Dim objReader As StreamReader = New StreamReader(fileName)
            'your delimiter
            Console.WriteLine("header column count = {0}", objReader.ReadLine().Split(",").Count)
            objReader.Close()
        End If


    End Sub

Answer 4

I would suggest  just on the fly load in a global temp first, then

select count(*)
  from sys.columns  where object_id = object_id('tempdb..dbo.##yourtable')  and put it in a variable...  and you can do you logic... if @vColCount =18 then this or what ever

---

Please mark as helpful and propose as answer if you find this as correct!!! Thanks,Miss Never Giveup .