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

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 dat开发者_运维知识库e 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 place 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.

SSIS exports non-string data types to Flat Files by implicitly converting them to strings - because Flat Files can only store string values. Since the conversion is implicit, there isn't anywhere for you to control that conversion. You need to make the conversion to a string explicit, and then you can put it under your control so you get the format you want. Add a Derived Column component prior to your Destination. In that Derived Column, explicitly convert your numbers, for example, create a new column and use an expression such as RIGHT("000000" + (DT_WSTR, 6)[EmpNumber], 6) to get a leading-zero filled string. In your Flat File Destination, you'll have to map this NEW column instead of EmpNumber. You'll also have to modify the Flat File Connection Manager to change the data type to a string of length 6 (otherwise you'll get validation errors).