Error while Concatenating two columns (date and time) of excel to SQL Server table using SSIS

• vs_pradip

  Old Hand

  Points: 337

  More actions

  December 3, 2010 at 9:53 am

  #227853

  Hi,

  I have a Excel which contains 2 columns 'Run_Date' and 'Run_Time' that contains the data as below,

  Run_Date Run_Time

  12/30/20093:54:19 AM

  I want to concatenate while pushing it to SQL Server 2005 table.

  I used the following steps, but it throws error.

  Excel source --> Derived Column

  in Derived column i do the following. Add a new column with the expression -> Run_Date+' '+Run_Time and assign it to a string data type (String [DT_STR]).

  From Derived Column --> Data Conversion to convert back to datetime format and then load it in my table which has datetime datatype for that column. but the error is,

  Error at Load HLCF UMA output [Derived Column [380]]: Parsing the expression "Run_Date+' '+Run_Time" failed. The single quotation mark at line number "1", character number "10", was not expected.

  Error at Load HLCF UMA output [Derived Column [380]]: Cannot parse the expression "Run_Date+' '+Run_Time". The expression was not valid, or there is an out-of-memory error.

  Error at Load HLCF UMA output [Derived Column [380]]: The expression "Run_Date+' '+Run_Time" on "output column "Run_DateTime" (560)" is not valid.

  Error at Load HLCF UMA output [Derived Column [380]]: Failed to set property "Expression" on "output column "Run_DateTime" (560)".

  (Microsoft Visual Studio)

  ===================================

  Exception from HRESULT: 0xC0204006 (Microsoft.SqlServer.DTSPipelineWrap)

  Please help me what should I have to do to address this error and concatenate my string as well.

  Thanks,

  Pradip

• Phil Parkin

  SSC Guru

  Points: 247154

  More actions

  December 3, 2010 at 9:56 am

  #1258531

  Try using double quotes " instead of single in your expression.

  ---

• vs_pradip

  Old Hand

  Points: 337

  More actions

  December 3, 2010 at 10:14 am

  #1258538

  Thanks So much Phil. That is working great. But I have one more problem.

  When i concatenate the date time into a single column in the derived column task as a String (DT_STR), the length it takes is 17, while i use a data conversion task and try to convert it to "Date[DT_DATE]" datatype, it is throwing an error as follows,

  [Data Conversion [513]] Error: Data conversion failed while converting column "Run_DateTime" (560) to column "Copy of Run_DateTime" (569). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

  Please advise what to be done on this as I cannot find a datatype datetime? The conversion task either has date or time or dbtimestamp.

  Thanks,

  Pradip

• Phil Parkin

  SSC Guru

  Points: 247154

  More actions

  December 3, 2010 at 10:30 am

  #1258543

  Whenever I do date stuff like that, I tend to put it into universal format first (YYYY-MM-DD HH:MM:SS) and then just map it directly to the destination column - no need for a data conversion.

  ---

• vs_pradip

  Old Hand

  Points: 337

  More actions

  December 3, 2010 at 10:47 am

  #1258553

  Phil need your help again.

  I know that SELECT convert(datetime, '2016-10-23 20:44:11', 121)

  converts to the format which you have mentioned (YYYYMMDD HH:MM:SS)

  can you please tell me how i can incorporate in derived column task?

  Thanks a ton,

  Pradip