I have a flat file as my source column0 STRING[DT_STR] length 50 column 1 STRING[DT_STR] length 50 column 2 STRING[DT_STR] length 50 and destination is a table with column0 nvarchar(24), column1 nvarchar(40) ,column2 datetime .............how to write an expression to trim and convert to nvarchar?

Hi Anu, Use data conversion from their you can trim and convert data types. Thanks

Anu, Why do you want to change the types to nvarchar? As long as your column types in Flat File are [DT_STR] and the types of columns in the destination table (SQL Server table, right?) are nvarchar, you can easily connect the data flow arrow from Flat File source into ADo.NET (or OLE DB) destination. You just might get the warning that some of the data might be truncated.Please mark as answer if this helps. Thank you. http://thebipalace.wordpress.com

Three options for cast: 1) Use cast exspression in derived column: (DT_WSTR,3)"Cat" 2) Use a data conversion transformation. 3) Use a cast in the source query (only works for database sources) Option 1 and 2 create an extra column, 3 doesn't. Trim: Trim option in the derived column: TRIM([column]) Trim and cast: (DT_WSTR,24)(TRIM([column0])) and (DT_WSTR,40)(TRIM([column1])) Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

Please mark posts as answered where appropriate

Thanks for the exprsssions...I am able to cast the string to nvarchar ..........but how to convert string(50) to datetime or date...the format of the source text file date field is yyyy mm dd Tried DT_Datetime and DT_DateTimeStamp both are not working (FYI-SSIS 2008)

The format of the string needs to be YYYY-MM-DD. You can use Substring + concatenation to build the right expression. Something like this: (DT_DBTIMESTAMP)(SUBSTRING(Column,1,4) + "-" + SUBSTRING(Column,5,2) + "-" + SUBSTRING(Column,7,2)) Arthur My Blog

(DT_DATE)(SUBSTRING([YourColumn],1,4) + "-" + SUBSTRING([YourColumn],5,2) + "-" + SUBSTRING([YourColumn],7,2)) Or (DT_DBTIMESTAMP)(SUBSTRING([YourColumn],1,4) + "-" + SUBSTRING([YourColumn],5,2) + "-" + SUBSTRING([YourColumn],7,2)) More info at: http://msdn.microsoft.com/en-us/library/ms141036.aspx Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

My dateformat id yyyymmdd how do I convert that to yyyy-mm-dd

(DT_DBTIMESTAMP)(SUBSTRING(@[User::StringVar],1,4) + "-" + SUBSTRING(@[User::StringVar],5,2) + "-" + SUBSTRING(@[User::StringVar],7,2)) gives 2/11/2011 12:00:00 AM if the StringVar is 20110211Arthur My Blog

My dateformat id yyyymmdd how do I convert that to yyyy-mm-dd (DT_DATE)(SUBSTRING([YourColumn],1,4) + "-" + SUBSTRING([YourColumn],5,2) + "-" + SUBSTRING([YourColumn],7,2)) If your date is 20110210 (yyyymmdd) Position: 1234567890 Value: 20110210 SUBSTRING([YourColumn],1,4) => 2011 SUBSTRING([YourColumn],5,2) => 02 SUBSTRING([YourColumn],7,2) => 10Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com