Solved

SSIS - format date

Posted on 2014-04-08
6
296 Views
Last Modified: 2016-02-10
Hi,

I am new to SSIS, I have SQL 2008 and VS 2008

I am trying to import an excel file into SQL, the datatype of the field is DATE, in the excel, the data looks like this

20140804
20130105
20091025

How can I convert it in SSIS during importing process?  thx
0
Comment
Question by:mcrmg
  • 3
  • 2
6 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
You should not have to.  It should be implicit.
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
>How can I convert it in SSIS during importing process?
I recommend against this.  If something throws an error during a combined import and conversion process, then it's more work for you to figure out what generated the error: the data being imported, or the SSIS conversion.

A more supportable approach would be to import it all as a varchar field into a staging table, then once it's imported successfully perform your conversions, to include a methodology to report a conversion failure, e.g. 20140231 not being a valid date.
0
 

Author Comment

by:mcrmg
Comment Utility
Once I imported them into a staging table, would you recommend I do the conversions in SQL via query or do it in SSIS?

Also, assume there is only one field I need to convert, eg. from 20120522 to 5/22/2012.

What would you recommend to "correct" all the data type, as your post, initially would be all varchar most likely.  Should I create a new table with correct data type then import data from staging table to this table?  thx
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 65

Accepted Solution

by:
Jim Horn earned 400 total points
Comment Utility
>would you recommend I do the conversions in SQL via query or do it in SSIS?
Yes, but I'll admit that that's a style preference, so your mileage may vary.
It's easier in T-SQL, plus I can do fancy-schmancy things like incrememt a validation_errors (int) column if anything fails a validation, and add English-sounding messages to a validation_message (varchar(1000)) column, then those can be used as the basis of the 'what failed validation?' T-SQL and messanging.

>Also, assume there is only one field I need to convert
In that case, it would be easy enough to have just one table, with columns your_date varchar(100), and your_date_edited (date), and do the conversion as an UPDATE statement.
0
 

Author Comment

by:mcrmg
Comment Utility
thank you very much
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

In couple weeks ago, I encountered an extremely difficult problem while deploying 2008 SSIS packages to a new environment (SQL Server 2014 standard).  My scenario is: We have one C# application that is calling 2008R2 SSIS packages to load text fi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now