Adding Record Count to flat file

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_Control Flow Tasks

  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 –

2_ScriptTask_AddingVariables

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.

3_Results

 

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s