I am writing this post in response to one of the questions posed in StackOverFlow. The task is to add record count info a generated flat file with some static text.

Let’s create one simple package to start with.

Firstly we will need two variables –

• intRecordCount – Data Type – Int – to store the record count
• strDestinationFullFilePath – Data Type – String – this holds the full file path of the destination file

I have two tasks in it as shown below –

1. Data Flow Task – Get Sample Data – This task has three Data Flow Tasks in it-
   • OLE DB Source – OLE_SRC – Get Data. The source data is a simple static value that I have hardcoded which returns Id and Name as shown below
   • Row Count – assign the value to the variable intRecordCount.
   • Flat File Destination – FFD_Load Data
2. Script Task  – Append Record Count. – This task does the job of reading through the file and appending the record count.

SELECT 1 AS 'Id', 'Karthik' AS 'Name'
UNION ALL
SELECT 2 AS 'Id', 'Pratap' AS 'Name'
UNION ALL
SELECT 3 AS 'Id', 'Ravi' AS 'Name'
UNION ALL
SELECT 4 AS 'Id', 'Gautam' AS 'Name'

 

 

Let’s look bit more into the script task as that is where we need to do the actual stuff. The first step is to first have the two variables as part of ReadOnlyVariables list as shown below –

Now in the the actual script, add the library System.IO in the using section. Now under Main() paste the following code –

public void Main()
{
string strDestinationFullFilePath = Dts.Variables["User::strDestinationFullFilePath"].Value.ToString();
string strRecordCount = Dts.Variables["User::intRecordCount"].Value.ToString();

using (StreamWriter sw = File.AppendText(strDestinationFullFilePath))
{
sw.WriteLine("Total Records - " + strRecordCount);
}

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

That’s it. Now execute the package and check the results by opening the resultant data. That’s how you can append the trailer records with the record count at the end of the text.