Search code examples
sql-serverssisssmsetl

Data size difference in SSIS output and SSMS output

I have an SSIS package which extract data using SQL command from a database and then generate delimited text file which is 1452kb

I run the same query on SSMS and generated text file which is 1499kb

Both data gives same number of rows,what could be the reason behind the file size difference and way to resolve this

Solution

  • Assuming that the files contains the same data. There are many things that can make size different:

    • Encoding: If using Unicode it may store additional data like The byte order mark (BOM)

    • Row Delimiter: It's really just about which bytes are stored in a file. CR is a bytecode for carriage return (from the days of typewriters) and LF similarly, for line feed. It just refers to the bytes that are placed as end-of-line markers. CRLF is the combination of the two character.

    • Empty Rows: Sometimes data exporter may add empty Rows at the End of the file

    • Header: If one file contains columns header and the other doesn't

    Hope it helps