IF Object_ID('tempdb..#staging') IS NOT NULL
  BEGIN
    DROP TABLE #staging
  END

-- Create dumping table with arbitrary surrogate key
CREATE TABLE #staging (
   id   int identity
 , data varchar(max)
)

-- Test data
INSERT INTO #staging (data) VALUES ('one1,two1,20100504,four1,five1,6')
INSERT INTO #staging (data) VALUES ('one2,two2,20101211,four2,five2,6,7,eight3')
INSERT INTO #staging (data) VALUES ('one3,two3,20109999,invalid,date,6,7,eight3,9,ten3')

DECLARE @delimiter varchar(2)
    SET @delimiter = ','

-- Split, based on delimiter, each row in to multiple rows
; WITH split AS (
  SELECT id
       , SubString(data, CharIndex(@delimiter, data) + Len(@delimiter), Len(data)) + @delimiter As data
       , Left(data, CharIndex(@delimiter, data) -1) As value
       , 1 As level
  FROM   #staging
    UNION ALL
      SELECT a.id
           , Right(b.data, Len(b.data) - CharIndex(@delimiter, b.data))
           , Left(b.data, CharIndex(@delimiter, b.data) - Len(@delimiter))
           , b.level + 1
      FROM   #staging As a
       INNER
        JOIN split As b
          ON b.id = a.id
      WHERE  CharIndex(@delimiter, b.data) > 0
)
, pvt AS ( -- Pivot the resultant columns - note I am only doing six because these are the common columns I'm interested in
  SELECT id
       , Max(CASE WHEN level = 1 THEN value END) As col1
       , Max(CASE WHEN level = 2 THEN value END) As col2
       , Max(CASE WHEN level = 3 THEN value END) As col3
       , Max(CASE WHEN level = 4 THEN value END) As col4
       , Max(CASE WHEN level = 5 THEN value END) As col5
       , Max(CASE WHEN level = 6 THEN value END) As col6
  FROM   split
  GROUP
      BY id
) -- Convert types and what not giving final result
SELECT col1
     , col2
     , Convert(datetime, CASE WHEN IsDate(col3) = 1 THEN col3 END) As col3
     , col4
     , col5
     , Convert(int, CASE WHEN IsNumeric(col6) = 1 THEN col6 END) As col6
FROM   pvt

IF Object_ID('dbo.parse_delimited_strings', 'TF') IS NOT NULL
  BEGIN
    DROP FUNCTION dbo.parse_delimited_strings
  END
GO

CREATE FUNCTION dbo.parse_delimited_strings (
   @string    varchar(Max)
 , @delimiter varchar(2)
)
  RETURNS @values table (
     value    varchar(255)
   , position int
  )
AS
  BEGIN
    ; WITH cte AS (
      SELECT 0 As [pos]
           , 1 As [level]
        UNION ALL
          SELECT Convert(int, CharIndex(@delimiter, @string, cte.pos + 1))
               , level + 1
          FROM   cte
          WHERE  CharIndex(@delimiter, @string, cte.pos + 1) > 0
    )

    INSERT INTO @values (value, position)
    SELECT Convert(varchar(255), SubString(@string, a.pos + 1, Coalesce(b.pos - 1, Len(@string)) - a.pos)) As [value]
         , a.level
    FROM   cte a
     LEFT
      JOIN cte b
        ON a.level + 1 = b.level

    RETURN
  END
GO

-- Split 'em
SELECT a.id
     , b.position
     , b.value
FROM   #staging As a
 CROSS
 APPLY dbo.parse_delimited_strings(a.data, ',') As b

-- Pivot
SELECT id
     , Max(CASE WHEN position = 1 THEN value END) As col1
     , Max(CASE WHEN position = 2 THEN value END) As col2
     , Max(CASE WHEN position = 3 THEN value END) As col3
     , Max(CASE WHEN position = 4 THEN value END) As col4
     , Max(CASE WHEN position = 5 THEN value END) As col5
     , Max(CASE WHEN position = 6 THEN value END) As col6
FROM   (
        SELECT a.id
             , b.position
             , b.value
        FROM   #staging As a
         CROSS
         APPLY dbo.parse_delimited_strings(a.data, ',') As b
       ) As x
GROUP
    BY id