Count number of records in CSV fil, compare it with count in file header using SSIS 2012.

  • tush002sunny

    SSC Veteran

    Points: 200

    Hi,

    I'm stuck with the thing where I don't know what to use for reading count in header of .csv file as source and compare it with acutal number of records within that file.

    If this comaprison is successful, I want to load that data from CSV file into a sQL table.

    I tried using Script component for validation, but no luck as of now.

    Can someone please suggest with solution?

    Thanks inadvance !!

  • John Mitchell-245523

    SSC Guru

    Points: 148353

    Have you tried using Powershell? Build the command with an expression, then use an Execute Process task to run it.

    John

  • p.shabbir

    SSCrazy

    Points: 2404

    tush002sunny (9/21/2015)


    Hi,

    I'm stuck with the thing where I don't know what to use for reading count in header of .csv file as source and compare it with acutal number of records within that file.

    If this comaprison is successful, I want to load that data from CSV file into a sQL table.

    I tried using Script component for validation, but no luck as of now.

    Can someone please suggest with solution?

    Thanks inadvance !!

    Sunny,

    What do you mean by "reading count in header of .csv file"? Do you mean "Number of header columns" in a .csv file? If that so, use can do it using script task.

    How many columns does your valid file contains?

    The code below will count the no of columns from the source file. This code is in C#.

    String sColnames = Dts.Variables["User::COLUMN_NAMES"].Value.ToString();

    String sFilePath = Dts.Variables["User::FILEPATH"].Value.ToString();

    using (StreamReader sr = new StreamReader(sFilePath))

    {

    String Column_Names = sr.ReadLine();

    var TotalCols = Column_Names.Split(',').Length;

    if (TotalCols == 21) //21 = valid header column count,give your valid header count.

    {

    Dts.Variables["ValidFlag"].Value = 1;

    //MessageBox.Show(Dts.Variables["ValidFlag"].Value.ToString());

    }

    else

    {

    Dts.Variables["ValidFlag"].Value = 0;

    //MessageBox.Show(Dts.Variables["ValidFlag"].Value.ToString());

    }

    }

  • REDDY2244

    Grasshopper

    Points: 11

    here i attached the file u can copy the code and execute it , u can get all the solution what u want.

    in name space u can add

    using System.IO;

    using System.Data.SqlClient;

    using Microsoft.VisualBasic.FileIO;

    and add reference as microsoft.visualbasic

     

    okay.

    thank u

    • This reply was modified 2 months, 3 weeks ago by  REDDY2244.
    Attachments:
    You must be logged in to view attached files.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply