3

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?

Hadi
  • 36,233
  • 13
  • 65
  • 124
0537
  • 993
  • 8
  • 15
  • 32

2 Answers2

1

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.

user3662215
  • 526
  • 4
  • 12
1

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

Hadi
  • 36,233
  • 13
  • 65
  • 124