How to set length for a numeric column and datetime in SSIS?

Question

How to set length for a numeric column and datetime in SSIS?

varun_2003

SSC Journeyman

Points: 93
More actions
August 6, 2011 at 12:25 am

#245776

I do have a flat file(csv file) which is extracted and loaded to another flat file by its position. For example I do have 3 columns like Record type, Emp number and date with a position constraint. Like Record type is of length 2 with a data type of alpha , Emp number is of length 6 with a data type of numeric and date with a length type of 26 in the format of yyyy-mm-dd.hh.mm.ss.000000.
So my result in the flat file would look like this
016543092011-08-05.11.08.34.000000
026540392011-08-05.11.10.59.000000
I do have two issues
First is I am not able to set the length of the numeric to 6 when I extract the csv file as when I specify the data type of Emp number as numeric then my length is blocked and only the precision is allowed for me to change in Flat file source and also in the Data conversion too. And the result is that there is more spaces added next to this field which is not needed in my output file
Next one is with the datetime --> i am not able to set the length for this one once i set this column with a data type of date time with precision.
With my current result I get the out put like this (I am not able to avoid the blank spaces which comes in between as I am not able to set the length for both numeric and also date time)
01654309 2011-08-05.11.08.34.000000
02654039 2011-08-05.11.10.59.000000
Please suggest me different ways to handle this issue. I have tried using a staging table in between this process but the issue is still prevailing.
Thanks in advance.
Regards,
varun

Viewing 3 posts - 1 through 2 (of 2 total)

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

rgtft Hall of Fame Points: 3518 More actions · Answer 1

I'm not totally following what you're asking, but if length is so important couldn't you bring this all in as strings (or one big long string) and parse it out from there and then convert to your datatypes later in the dataflow using a derived column.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

BCP with a BCP formatfile would do this easily and with great speed. You could also create a VIEW of the data were the column datatypes are CHAR(2), CHAR(6), etc. Last, but not lease, you could simply CAST the data to the appropriate length CHAR() as you're exporting.

BTW... this type of file is commonly refered to as a "Fixed Field" or "Fixed Length" file.

--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)