August 18, 2008 at 10:49 pm
hi guys,
i am having same problem but right now it is string to date
my source is csv file in that birthdate column is string datatype(20060228) and i have to import it into sql server destination and data type is date(2006/02/28)
for this requirement i used sql server import and export wizard in sql server management studio.but it shows below error
error:The value could not be converted because of a potential loss of data
my friend was suggested me use script component in business intelligence studio. but i dont know how to use script component and code tell me how to use script component and related code .pls its urgent
any help appricated
vasu
August 19, 2008 at 1:26 am
You have to do this in BIDS. I do not believe the import wizard gives you much flexiblity.
You need to use a derived column and then use split string functions to cut up the string and reformat it in a way SSIS understands. Which is yyyy/mm/dd
You could use a script component but the above is simplier.
Save the package created by the import wizard as a starting point.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 19, 2008 at 3:42 am
Hi proctor,
i used derived column transformation and i used below expression
(DB_DBDATE)(Substring(columnname,5,2)+"/"+Substring(columnname,7,2)+"/"+substring(columnname,1,4))
it works fine and change the date (2006-02-28) but it didnt change datatype
and one more thing is in my source i have 100 columns in this columns 25 columns are '00000000' how to import this columns into destination
i searched every forums but i didnt get any related matter
August 19, 2008 at 4:38 am
The conversion has to be done on a new, derived, column. The resultant should be date.
Is use (DT_DATE)(SUBSTRING(Col11,4) + "/" + SUBSTRING(Col15,2) + "/" + SUBSTRING(Col1,7,2))
which breaks it into a string of yyyy/mm/dd and this converts to any one of the date formats.
As for the 00000000, you would have to evaluate it first and if 0000000, set it to null else try parse it.
Unfortunately, you are going to have to do this to all columns.
if this is a once off thing, rather import into a tempt table as a varchar and do the SQL to do the conversion. Would be quicker.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 19, 2008 at 5:27 am
hi,
i used above expression in derived column but it came same problem
it didnt change datatype
and what is "import into a temp table as a varchar and do the sql to do the conversion"
plz tell me deatial i am little bit confusing about it
plz dont hesitate
August 19, 2008 at 5:43 am
Please (not plz) don't ask us to hurry along. People help people here in their own time. Nothing else.
If you say the code does bot work, post some examples of the source data. Post screen dump of the derived column.
As for the second option, anything more than this is actually doing it for you. Which I won't.
Import the raw data into a SQL table, for basic data types. Run a conversion on it from there into the final table.
Finally, read Jeff's article about etiquette: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Are these interview or exam questions?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 19, 2008 at 6:37 am
i am really sorry...proctor
August 19, 2008 at 6:52 am
Nothing to be sorry about. Post the source data, screen dumps of the derived column and we will help. That is why we are all here.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply