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

• tush002sunny

  Old Hand

  Points: 310

  More actions

  September 21, 2015 at 5:10 am

  #303309

  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: 148809

  More actions

  September 21, 2015 at 5:28 am

  #1828045

  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

  More actions

  September 25, 2015 at 4:21 am

  #1829386

  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

  More actions

  August 20, 2019 at 1:50 pm

  #3672760

  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 5 years, 4 months ago by  REDDY2244.

  Attachments:

  You must be logged in to view attached files.