Want to add Recordcount in Flat File output

Question

Want to add Recordcount in Flat File output

haichells

SSCrazy

Points: 2713
More actions
November 1, 2012 at 3:55 am

#264969

Hi,
I have a requirement where i need to export my data to a flat file (.csv file) and at the end of the file i need to display the Total no of records. Is there any way to do this in an easy way.
Thanks in Advance!
Chelladurai

Viewing 8 posts - 1 through 7 (of 7 total)

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

Jeff Moden SSC Guru Points: 1004353 More actions · Answer 1

haichells (11/1/2012)
Hi,
I have a requirement where i need to export my data to a flat file (.csv file) and at the end of the file i need to display the Total no of records. Is there any way to do this in an easy way.
Thanks in Advance!
Chelladurai

What are you using to do the export of the flat file to begin with?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

haichells SSCrazy Points: 2713 More actions · Answer 2

I am using a OLEDB Source where i placed a query and the query output has been transfered to flat file (.csv)

Thank you

Chelladurai

Rich Mechaber SSChampion Points: 11130 More actions · Answer 3

I had a similar requirement to create a header line in a CSV output file, where the header line contained a total record count, sum($), etc.

I wound up dumping the detail rows to a temp table, then writing out the header line (aggregate query on the temp table), and finally the details.

This was in SSIS.

Rich

Jeff Moden SSC Guru Points: 1004353 More actions · Answer 4

haichells (11/1/2012)
I am using a OLEDB Source where i placed a query and the query output has been transfered to flat file (.csv)
Thank you
Chelladurai

You mean you're using SSIS to do this? Any chance of seeing the query? I might be able to help.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 5

It's annoying but doable. Annoying at the levels of if Jeff has a way to easily attach it via BCP do that.

However, the answer, in shorthand.

First, create a second datasource to the same file that is nothing but one giant column with the same row delimeters. You'll need this so you can append data to the tail later.

Next, just before your delivery to the flat file, mulicast the dataflow. Send one of these streams to a COUNT ALL (*) Aggregate component. You now have your choice of pain. Send this to a Script Component (destination) and feed it into an SSIS variable for later use, or dump it to a Recordset Destination.

If you dump it to a recordset destination, back in the main package, build off a ForEachLoop off that object (it'll only run once but that loop is how you access the object) and have it dump the result to a variable. If you already loaded the variable you don't need this.

Next, you create ANOTHER dataflow. This can be a SQL Query off your originating server or you can use a script component source. I recommend the SQL.

Now, your OLEDB source will be a SQL command, something like SELECT 'Rowcount for File: ' + ?. Send in the count variable as your parameter.

Feed this to the new extra datasource you built for the flat file, the one that doesn't CSV or anything like that. Make sure it's set to APPEND the data.

If that sounds complex, annoying, and nasty... it's because it is. What you're looking to do is create an oldschool check for the end of a mainframe file, which on mainframes which fed a file via Cobol was a simple thing for it to do, everything else was complicated. Now it's inverted. Be aware, that 'check' row at the end pretty much breaks any input csv feeders that might want to use it.

What I personally did when this was thrust upon me was to use a second file to allow them their check values. This kept 'garbage data' out of the source/destination file while still allowing them a way to retrieve their parity check. You'd perform the task similarly but at least you're not corrupting the resultant file from other CSV readers, and if you wanted to get fancier you could immediately append the results to the check file with a derived column feeding into it to append whatever text-verbage you needed to prepend it.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Jeff Moden SSC Guru Points: 1004353 More actions · Answer 6

Evil Kraig F (11/2/2012)
It's annoying but doable. Annoying at the levels of if Jeff has a way to easily attach it via BCP do that.

BWAA-HAAA! You want that with or without column headers? True CSV or "Comedy Delimited"? 😀

I guess that's what I need to know next. And do you want the "count" to just be a number that appears on the line of do you want some qualifying text to go with it?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004353 More actions · Answer 7

As Evil Kraig F will tell you, I don't do this type of thing with SSIS. As a result, I don't know much about SSIS. I do know that there's some sort of "task" that will allow you to execute a command line script, though.

I normally do this type of thing just with T-SQL and xp_CmdShell via an EXECUTE AS OWNER stored proc. I will also tell you not to use xp_CmdShell unless your system is properly locked down (no non-DBA user or login or app login has "SA" privs) and never give user or login privs to run xp_CmdShell directly.

Shifting back to an easy "SSIS Solution", let's say that we wanted to export the Address, City, State, and ZIP columns of AdventureWorks.Person.Address table (SQL Server 2005 version) as a "true CSV" with double quotes and the whole 9 yards including a column header and the final "line" count. The first thing I'd do is build a stored procedure to select the data and do all the formating we need like the following.

--===== Identify the database to create the stored proc in.

USE AdventureWorks;

GO

CREATE PROCEDURE dbo.CreateCSV01 AS

--===== Create a table variable to hold the CSV data in.

-- If we use a temp table, it might not work if

-- called from a CMD shell (however it's called).

DECLARE @Output TABLE

(

RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

CSVData VARCHAR(8000)

)

;

--===== Create the header and the data

INSERT INTO @Output

(CSVData)

SELECT '"Address","City","State","ZIP"'

UNION ALL

SELECT QUOTENAME(AddressLine1,'"')+','

+ QUOTENAME(City,'"')+','

+ QUOTENAME(StateProvinceID,'"')+','

+ QUOTENAME(PostalCode,'"')

FROM Person.Address

;

--===== Create the total line using the rowcount

-- from above.

INSERT INTO @Output

(CSVData)

SELECT 'Total Line Count Including This Line = '

+ CAST(@@ROWCOUNT+1 AS VARCHAR(10))

;

--===== Return the content of the OUTPUT table

-- so BCP can pick it up.

SELECT CSVData FROM @Output ORDER BY RowNum

;

GO

Then, you can add the following BCP command to your command shell script task in SSIS and Bob's your uncle.

bcp "EXEC AdventureWorks.dbo.CreateCSV01" queryout "C:\Temp\Contacts.txt" -c -CRAW -T -Syourserve\instancenamehere

To make a long story short, the BCP command just executes the stored procedure to create the necessary output and routes it to the given file path which could also be a UNC to a file share.

Of course, you'll need to change the target file to what is proper and you'll need to change the -S parameter to the proper server\instance name.

Please see Books Online for what the purpose of the other parameters are.

As a bit of a sidebar, keep in mind that QUOTENAME is only good for 258 characters including the 2 "wrapper quotes" and any embedded quotes which will be doubled-up as an "escape" mechanism. You'll have to do a manual "Replace'n'Wrap" for double quotes if you have a column longer than that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)