SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


conversion column string to date in ssis


conversion column string to date in ssis

Author
Message
vasu.ssis
vasu.ssis
Right there with Babe
Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)

Group: General Forum Members
Points: 725 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
Crispin Proctor
Crispin Proctor
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7805 Visits: 414
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!
vasu.ssis
vasu.ssis
Right there with Babe
Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)

Group: General Forum Members
Points: 725 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
Crispin Proctor
Crispin Proctor
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7805 Visits: 414
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!
vasu.ssis
vasu.ssis
Right there with Babe
Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)

Group: General Forum Members
Points: 725 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
Crispin Proctor
Crispin Proctor
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7805 Visits: 414
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!
vasu.ssis
vasu.ssis
Right there with Babe
Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)

Group: General Forum Members
Points: 725 Visits: 90
i am really sorry...proctor
Crispin Proctor
Crispin Proctor
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7805 Visits: 414
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search