Unable to load leading zeros into CSV

Question

Unable to load leading zeros into CSV

MSSQLBuddy

SSCrazy

Points: 2354
More actions
March 13, 2009 at 2:43 am

#201555

Hi All,
My package is transferring records from SQL Server to CSV file.
There is a column called
Column Name Data Type Value
Number Varchar 0000000563
I m unable to load the leading zeros into CSV file. The CSV loads from the first non -zero value like 563 from the last value.
Any help greatly appreciated
Thanks in advance
Regards
Priya

Viewing 10 posts - 1 through 9 (of 9 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Can you post the details of how you are doing this? What are all the steps in the SSIS package/dataflow? Are you sure the data is being retrieved from the database using the right type and that there are no type changes within the flow?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Ray M SSC-Insane Points: 21093 More actions · Answer 2

Double check in your flat file destination that the data type for that column is a String.

Jason Coleman SSCrazy Points: 2187 More actions · Answer 3

I am also having a very similar problem.

I have a numeric field, (18,2) that needs to be exported to a csv file, but when it gets exported any zero values are showing as '.00' and not '0.00' . i.e. the leading zeros are missing.

The connection manager for the Flat file has the column specified as a string. Putting a Data Viewer before the final Flat File Destination task shows the data in the correct format, but previewing the columns in the Flat File destination, shows the value without its leading zero....

Any ideas on how i can get this leading zero back in...Is there any particular format I need to use?

Phil Parkin SSC Guru Points: 246962 More actions · Answer 4

You could try putting a data conversion task in - to cast your numeric field as string - and then map the converted field to your destination, after manipulating the format if necessary.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jason Coleman SSCrazy Points: 2187 More actions · Answer 5

Have also tried that using a data conversion tool. But it does not seem to make any difference whether the format of the field has been set in a string or numeric format.

I'm pretty stuck.

Phil Parkin SSC Guru Points: 246962 More actions · Answer 6

Once you have a string field which contains, for example .75

you can add a derived column that prepends a 0

the string is then 0.75 and that is what will go into your destination.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jason Coleman SSCrazy Points: 2187 More actions · Answer 7

Yep...thats finally what I have had to do.

Seems strange though that the final task seems to transform the data on its own. even if u tell it not to!

Phil Parkin SSC Guru Points: 246962 More actions · Answer 8

Jason Coleman (4/9/2010)
Yep...thats finally what I have had to do.
Seems strange though that the final task seems to transform the data on its own. even if u tell it not to!

That's just SSIS trying to be helpful:-D

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

RexSmith Ten Centuries Points: 1027 More actions · Answer 9

RexSmith

Ten Centuries

Points: 1027

April 15, 2010 at 4:16 am

#1151988

any luck priya?

Rex Smith