SSIS - format date

Posted on 2014-04-08
Medium Priority
Last Modified: 2016-02-10

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


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

Expert Comment

by:Anthony Perkins
ID: 39985758
You should not have to.  It should be implicit.
LVL 66

Expert Comment

by:Jim Horn
ID: 39985777
>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.

Author Comment

ID: 39985880
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
Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

LVL 66

Accepted Solution

Jim Horn earned 1600 total points
ID: 39985913
>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.

Author Comment

ID: 39985928
thank you very much
LVL 66

Expert Comment

by:Jim Horn
ID: 39985945
Thanks for the grade.  Good luck with your project.  -Jim

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Here's a requirements document template for an integration project (also known as Extract-Transform-Load or ETL) based on my development experience as an SQL Server Information Services (SSIS) developer over the years.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

624 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