January 15, 2011 at 3:30 pm
Hi guys,
I need your guidance/help. I have source data type "text" and target field data type is "varchar" Please guide me how i can change data type text to varchar in SSIS. I already tried "Data Conversion" Transformation but no luck. Please reply ASAP big help for me. Stuck middle of the project. Appreciate in advance.
January 16, 2011 at 2:45 am
What do you mean with no luck when using the data conversion component?
Where there any errors? if yes, please post them here.
Try changing the data type in the advanced editor of the source (don't know it that will work).
If all options don't work, you can stage the data and do the conversion in SQL Server. (remember that varchar has an 8000 character limit)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 16, 2011 at 3:35 am
Varchar of what length?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 16, 2011 at 9:48 pm
I tried all tips that i know, use derived column/advance editor/ and so on, My source has text data type field (I can't change it) and i have to map this field to my target db and that field has data type varchar(2000). Any expression i can use in derived column to convert data type text to varchar?
January 16, 2011 at 10:19 pm
(DB_STR, <code page>, 2000) <Source column name> should work. Replace <code page> with the code page you're using and <Source column name> with the name of the source column
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 17, 2011 at 6:47 am
Gila Thanks for your reply, Please tell me what do you men by "Codepage" i didn't get that part.
January 17, 2011 at 6:56 am
rocky_498 (1/17/2011)
Gila Thanks for your reply, Please tell me what do you men by "Codepage" i didn't get that part.
Quoting BOL:
A code page is an ordered set of characters of a given script in which a numeric index, or code point value, is associated with each character. A Windows code page is commonly referred to as a character set or charset. Code pages are used to provide support for the character sets and keyboard layouts that are used by different Windows system locales.
For example, using the following expression:
(DT_STR,30,1252)
It casts 30 bytes, or 30 single characters, to the DT_STR data type using the 1252 code page.
(the example comes from this msdn page, read it for more information)
More information on the 1252 codepage (I think this one is the most widely used):
http://en.wikipedia.org/wiki/Windows-1252
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 18, 2011 at 9:54 pm
Appreciate for your help!
January 19, 2011 at 12:01 am
rocky_498 (1/18/2011)
Appreciate for your help!
No problem, glad to help!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply