CODEWITHSUNDEEP
c#sql-servercsvssisssis-2012
Michael
Michael

Reputation: 2657

SSIS Script Task get row count of csv files

I'm using VS 2012 \ SQL SSIS. How do I retrieve the row counts of records in csv files and assign them to a variable (RowCount) to be use later in the process? I would also need to exclude 1 from the count as this will be the column header.

However, I cannot just use a Data Flow Task, OLE Source then a Row Count as I do not know the filenames (or headings of the csv's). All I know is there will be at least row header. So far I have a FOR EACH LOOP Container that looks into a specific folder and then assigns the filename to a variable "FileNameFound". How do I configure the Script Task to then assign the RowCount to Variable "RowCount"?

enter image description here

So far in my Script Task (C#) I have tried the below just to test with a message box

public void main()
{
        string strPath = Dts.Variables["FileNameFound"].Value.ToString();
        string[] strArr = File.ReadAllLines(strPath);
        //MessageBox.Show(strArr[0]);
        MessageBox.Show("Total Records " + strArr.Length.ToString());

        Dts.TaskResult = (int)ScriptResults.Success;
}

How do I assign the records counted to the SSIS Variable "RowCount"?

Upvotes: 0

Views: 4209

Answers (1)

billinkc
billinkc

Reputation: 61211

How do I assign a value to an SSIS variable in a script task?

Assuming you have a variable named @[User::RowCount] in the Script Task configuration page, it has a place to identify the ReadOnly collection of variables as well as the Read/Write collection of variables. Add this to the ReadWrite collection.

In your actual code, you will assign the computed row count to the .Value property of an SSIS variable.

Dts.Variables["RowCount"].Value = strArr.Length -1;

Upvotes: 1

Related Questions