Hi all, Please help. I have an Excel file that I am importing into SQL Server via SSIS and I have a cell that is date formatted. My problem is that in the spreadsheet the value is 01/07/2011, meaning 1st July 2011. However when I import using SSIS in my dataflowtask using the data conversion component to save the type as a DT_DATE, the record in the database is showing as 2011-01-07 00:00:00:000, meaning 7th January 2011. How can I use SSIS to format the data that is inserted into SQL Server as DD/MM/YYYY so I get 1st July 2011? Any suggestions most welcome. Thanks in advance.

Make this column a string. Use a Derived Column Transformation instead to format it the way you need to fit into the database of yours.Arthur My Blog

use a Derived Column transform with this expression: (DT_DATE) (SUBSTRING(InputDateColumn,7,4)+"-"+ SUBSTRING(InputDateColumn,4,2)+"-"+ SUBSTRING(InputDateColumn,1,2)) Note that you should use your input date column name instead of InputDateColumn in the above expression.http://www.rad.pasfu.com

Heads up you may need to read you file using IMEX = 1 to test once you have the ball rolling (every thing working well) remove some dates from the first few cells of the excel and run the package see if it works. Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Hi Reza, that solution didn't exactly work for me. Was getting errors. However I have tweaked it slightly to the below; (DT_STR,50,1252)(YEAR((DT_DATE)[InputDateColumn])) + "-" + (MONTH((DT_DATE)[InputDateColumn]) < 10 ? "0" : "") + (DT_STR,50,1252)(MONTH((DT_DATE)[InputDateColumn])) + "-" + (DAY((DT_DATE)[InputDateColumn]) < 10 ? "0" : "") + (DT_STR,50,1252)(DAY((DT_DATE)[InputDateColumn])) ...however when the value for this is inserted into the tables it's still in the wrong format. i.e. when I use the following SQL statement... SELECT [InputDateColumn] AS [SqlServerDateTimeDataType] , REPLACE(CONVERT(VARCHAR(10), [InputDateColumn], 106), '/', '-') AS [DateTranslated] FROM [Table] ... I get "2011-01-07 00:00:00.000" , "07 Jan 2011" instead of "2011-07-01 00:00:00.000" , "01 Jul 2011" What am I doing wrong? Please help....

Heads up you can use Script component if you like, you need a script like........ basically you have to read the data as string and then amke a string date format of YYYY-MM-DD Try If String.IsNullOrEmpty(Row.CallDate.ToString) Then Throw New Exception("Error in the Date Column") End If Dim DateFormat As String Dim strYYYY As String Dim strMM As String Dim strDD As String Dim strLen As Integer Dim StartLen As Integer Dim EndLen As Integer Dim PosFirstSlash As Integer Dim PosSecondSlash As Integer 'MsgBox(Row.CallDate + " = main date") strLen = Row.CallDate.Length StartLen = strLen - 4 EndLen = strLen ''''''''''''''''''''''''''''''''''''''''''''''''''''''' PosFirstSlash = Row.CallDate.IndexOf( "/") PosSecondSlash = Row.CallDate.LastIndexOf( "/") '''''''''''''''''''''''''''''''''''''''''''''''''''''''' '' for the YEAR '''''''''''''''''''''''''''''''''''''''''''''''''''''''' 'strYYYY = Row.CallDate.Substring(StartLen, 4) strYYYY = Row.CallDate.Substring(PosSecondSlash + 1, 4) '''''''''''''''''''''''''''''''''''''''''''''''''''''''' '' month '''''''''''''''''''''''''''''''''''''''''''''''''''''''' strMM = Row.CallDate.Substring(0, PosFirstSlash) strMM = Trim(strMM) '' some cases we have (Space number _1) If strMM.Length = 1 Then strMM = "0" & strMM End If 'MsgBox(strMM + "str MM") '''''''''''''''''''''''''''''''''''''''''''''''''''''''' '' Dat '''''''''''''''''''''''''''''''''''''''''''''''''''''''' strDD = Row.CallDate.Substring(PosFirstSlash + 1, PosSecondSlash - 1 - PosFirstSlash) strDD = Trim(strDD) '' some cases we have (Space number _1) If strDD.Length = 1 Then strDD = "0" & strDD End If 'MsgBox(strDD + "str DD") ' Throw New Exception("Error in the Date format") DateFormat = strYYYY & "-" & strMM & "-" & strDD ' Redirect rows toward valid row output 'MsgBox(DateFormat) If Not IsDate(DateFormat) Then '''' Redirect rows toward valid row output Throw New Exception("Error in the Date format") End If Row.CallDate = DateFormat Row.DirectRowToValidRows() Catch ex As Exception ' Capture error description Row.ErrorDescription = ex.Message.ToString ' Redirect rows toward error row output Row.DirectRowToErrorRows() End TrySincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Hi Nik. Thanks for your help, it was helpful. I finally got the below code segment to work; (DT_STR,50,1252)(YEAR((DT_DATE)[InputDateColumn])) + "-" + (DAY((DT_DATE)[InputDateColumn]) < 10 ? "0" : "") + (DT_STR,50,1252)(DAY((DT_DATE)[InputDateColumn])) + "-" + (MONTH((DT_DATE)[InputDateColumn]) < 10 ? "0" : "") + (DT_STR,50,1252)(MONTH((DT_DATE)[InputDateColumn]))

Hi Reza, that solution didn't exactly work for me. Was getting errors. However I have tweaked it slightly to the below; could you put Data VIEWER after derived column and check the output of derived column ? just right click on the green arrow between derived column and next transformation/destination and select data viewer as grid ( default option ), now you can see values in runtime.http://www.rad.pasfu.com

Thanks Reza, as your initial help let me to the answer I was looking for.