1

I am asked to export data from excel to flat file using ssis package. I have columns in excel file that is in mm/dd/yyyy date format but I want to convert it into yyyymmdd in the flat file. I have tried all measures that I know of but I am not to do it.

Can someone help me ?

Thanks

apariyar
  • 35
  • 2
  • 11
  • That's not an import. That's an EXPORT.If the dates are recognized as dates by excel, you can format their cells into whatever representation you want, and will be exported as such into the csv. – Marc B Jun 30 '14 at 17:57
  • May be I wrote an incomplete question. What I am trying to do is get data from different columns from a single excel file and then export to different csv templates with different or combination of columns. So I was wondering if there is a way to convert date format in mm/dd/yyyy to yyyymmdd into csv files using derived column transformation. – apariyar Jun 30 '14 at 18:22

2 Answers2

2

Use derived column transformation and tweak below expression as per your requirement. 

(DT_WSTR,4)YEAR(Date) + “-” + RIGHT(“0″ + (DT_WSTR,2)MONTH(Date),2) + “-” + RIGHT(“0″ + (DT_WSTR,2)DAY(Date),2)

To know more about how to use derived column, follow this article.

Converting Strings to Dates in the Derived Column Component

More about Expressions

if your input columns is string, you can use this expression:

((DT_WSTR,4)Year(((DT_DATE)InputColumn)))+
RIGHT("0"+((DT_WSTR,2)Month(((DT_DATE)InputColumn))),2)+
RIGHT("0"+((DT_WSTR,2)Day(((DT_DATE)InputColumn))),2)

but if your input column is date you can use this one:

((DT_WSTR,4)Year(InputColumn))+
RIGHT("0"+((DT_WSTR,2)Month(InputColumn)),2)+
RIGHT("0"+((DT_WSTR,2)Day(InputColumn)),2)
Vikramsinh Shinde
  • 2,742
  • 2
  • 23
  • 29
  • Vikramsinh, I have tried this expression, but the output never seem to change. Actually the output is always in the format mm/dd/yyyy. I know the expression is correct but I am not sure why I am getting the same error. Do you think it has to do with excel version or visual studio or anything? – apariyar Jun 30 '14 at 20:14
  • It's a simple expression and you are using it in derived column so i don't think VS or excel version is causing any issue. Try debugging solution by putting few breakpoints or just data viewer. Sometimes derived column transformation is tricky so check mapping as well. – Vikramsinh Shinde Jun 30 '14 at 21:17
  • Can you please have a quick look of this walk-though - http://sqlblog.com/blogs/andy_leonard/archive/2009/02/04/ssis-expression-language-and-the-derived-column-transformation.aspx – Vikramsinh Shinde Jun 30 '14 at 21:19
  • Error at Claim Data Flow Task [Derived Column [244]]: Attempt to parse the expression "(DT_WSTR,4)YEAR(IncidentDate) + “-” + RIGHT(“0″ + (DT_WSTR,2)MONTH(IncidentDate),2) + “-” + RIGHT(“0″ + (DT_WSTR,2)DAY(IncidentDate),2)" failed. – apariyar Jun 30 '14 at 22:07
  • The token " " at line number "1", character number "33" was not recognized. The expression cannot be parsed because it contains invalid elements at the location specified. – apariyar Jun 30 '14 at 22:08
  • That is the error I get. IncidentDate is the column header in excel file. – apariyar Jun 30 '14 at 22:08
  • there must be some typo. closely look for double quotes and + sign. I haven't tested those expression which are in my answer. So please have a closer look. or Try to evaluate expression. – Vikramsinh Shinde Jun 30 '14 at 22:13
  • Use this SSIS Expression Editor & Tester tool - http://expressioneditor.codeplex.com/Wikipage?ProjectName=expressioneditor – Vikramsinh Shinde Jun 30 '14 at 22:15
  • (DT_WSTR,4)YEAR((DT_DATE)IncidentDate) + RIGHT((DT_WSTR,2)MONTH((DT_DATE)IncidentDate),2) + RIGHT((DT_WSTR,2)DAY((DT_DATE)IncidentDate),2) This works but if the month is Feb and day is 2 then the answer is yyyy21. – apariyar Jun 30 '14 at 22:20
  • try this, just replace getdate() with IncidentDate - (DT_STR,4,1252)DATEPART( "yyyy" , getdate() ) + RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , getdate() ), 2) + RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , getdate() ), 2) – Vikramsinh Shinde Jun 30 '14 at 22:28
  • Thanks This works. (DT_WSTR,4)YEAR((DT_DATE)IncidentDate) + RIGHT("0" + (DT_WSTR,2)MONTH((DT_DATE)IncidentDate),2) + RIGHT("0" + (DT_WSTR,2)DAY((DT_DATE)IncidentDate),2) DataType :String(DT_STR) – apariyar Jul 01 '14 at 02:13
0

Select the cells in question and format them:

Custom > YYYYMMDD

and then SaveAs .csv

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • Well I know how to use custom setting, but is there a way to do it using derived column transformation? The one I am working on is single source multiple destinations flat files with different combination of data columns. – apariyar Jun 30 '14 at 18:10