CODEWITHSUNDEEP
sql-servercsvssisetlexport-to-csv
0537
0537

Reputation: 1033

include leading zeros while exporting to csv in ssis

I have a package which exports data from table to .csv file, it exports 600 000 records.

I have few columns which has leading zeros and trailing zeros, while exporting the data both leading and trailing zeros are missing.

EX: actual data coming from source 

 column1   column2  
 001       67.50

exported data in csv file 

 column1   column2  
 1         67.5

I tried many methods like placing data conversion, and also Replicate function in derived column.

i also tried by exporting the data to .txt file and use .cmd file to change the .txt file .csv file, all these methods yielded no results.

is there a way to achieve this?

Upvotes: 3

Views: 10102

Answers (2)

Hadi
Hadi

Reputation: 37313

Open the Flat File Connection manager used in the Destination csc, Go to Advanced Tab change all columns data type to DT_STR or DT_WSTR (Note that when data is saved to a flat file, there is no data type taken into consideration)

If it still not working, then add a derived column that convert these values to DT_STR before assigning them to Destination

Upvotes: 1

user3662215
user3662215

Reputation: 526

When exporting to text- or csv-formatted files numeric values that need to retain leading and/or trailing zeros, a combination of the following is required.

1) Using a derived column or conversion task under Data Flow, convert the numeric field to a text or varchar.

2) Be sure the destination CSV file has the field defined as a text of sufficient character length.

Lastly, if you are in a situation where a numerical value is required (NULL or blank are not acceptable) in the output file, then take a look at the following link. This is where a "0.00" must be provided instead of a NULL or blank.

How do I get SSIS Data Flow to put '0.00' in a flat file?

Hope this helps.

Upvotes: 1

Related Questions