convert string to nvarchar derived column transformation
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?
February 9th, 2011 10:53pm
Hi Anu,
Use data conversion from their you can trim and convert data types.
Thanks
Free Windows Admin Tool Kit Click here and download it now
February 9th, 2011 11:10pm
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
February 9th, 2011 11:18pm
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
Free Windows Admin Tool Kit Click here and download it now
February 10th, 2011 12:28am
Please mark posts as answered where appropriate
February 10th, 2011 1:35am
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)
Free Windows Admin Tool Kit Click here and download it now
February 10th, 2011 1:23pm
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
February 10th, 2011 1:33pm
(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
Free Windows Admin Tool Kit Click here and download it now
February 10th, 2011 2:02pm
My dateformat id yyyymmdd how do I convert that to yyyy-mm-dd
February 10th, 2011 2:27pm
(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
Free Windows Admin Tool Kit Click here and download it now
February 10th, 2011 2:30pm
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
February 10th, 2011 2:30pm