Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

conversion column string to date in ssis Expand / Collapse
Author
Message
Posted Monday, August 18, 2008 10:49 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 19, 2008 9:30 AM
Points: 23, Visits: 90
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
Post #554740
Posted Tuesday, August 19, 2008 1:26 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 14, 2014 3:32 PM
Points: 917, Visits: 412
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,
Crispin


I can't die, there are too many people who still have to meet me!

It's not a bug, SQL just misunderstood me!
Post #554773
Posted Tuesday, August 19, 2008 3:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 19, 2008 9:30 AM
Points: 23, Visits: 90
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
Post #554829
Posted Tuesday, August 19, 2008 4:38 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 14, 2014 3:32 PM
Points: 917, Visits: 412
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,
Crispin


I can't die, there are too many people who still have to meet me!

It's not a bug, SQL just misunderstood me!
Post #554842
Posted Tuesday, August 19, 2008 5:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 19, 2008 9:30 AM
Points: 23, Visits: 90
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

Post #554898
Posted Tuesday, August 19, 2008 5:43 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 14, 2014 3:32 PM
Points: 917, Visits: 412
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,
Crispin


I can't die, there are too many people who still have to meet me!

It's not a bug, SQL just misunderstood me!
Post #554907
Posted Tuesday, August 19, 2008 6:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 19, 2008 9:30 AM
Points: 23, Visits: 90
i am really sorry...proctor
Post #554954
Posted Tuesday, August 19, 2008 6:52 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 14, 2014 3:32 PM
Points: 917, Visits: 412
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,
Crispin


I can't die, there are too many people who still have to meet me!

It's not a bug, SQL just misunderstood me!
Post #554958
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse