dat file importing to sql server

My file has '|' as text qualifier and col delimiter as ',' but their is no row delimiter.how to handle it.

example of data

|1|,|4616002.000000|,|firstname1|,|lastname1|,||,|19341009000000|,|Female|,|HP|,|20090123160644|,|733.01|,|Osteoporosis, Postmenopausal.|,|ICD-9-CM|,||,||,||,||,|Confirmed|,|Active|,|20090123160632|,||,||,||,||,||,||,||,||,|Medical|,||

|2|,|4616002.000000|,|firstname2|,|lastname2|,||,|19341009000000|,|Female|,|HP|,|20090123160652|,|530.81|,|GERD [Gastroesophageal reflux disease]|,|ICD-9-CM|,||,||,||,||,|Confirmed|,|Active|,|20090123160644|,||,||,||,||,||,||,||,||,|Medical|,||

Solution

This process is further outlined below. As indicated, be sure to confirm that the appropriate data types are selected and that the source data conforms to them. I can't definitively say without knowing what data types you're trying to import the records as, but the error you're receiving may be data conversion related and I'd suggest checking any additional error messages and verifying that the proper data type and length is being used for each column.

Add a Data Flow Task on the Control Flow and create a Flat File Source component within this. Open to Flat File source and press the New button to define a Flat File Connection Manager using an example data file. On the General page (first screenshot) select the Delimited format and | as the Text Qualifier. Unless there are headers uncheck the Column Names in the First Data Row checkbox.
On the Columns tab (second screenshot) choose carriage return-line feed ({CR}{LF}) as the row delimiter. Line feed refers to moving to the next vertical line and carriage return places the cursor at the beginning of the horizontal line, such as when the Enter key is pressed on Windows. Also choose a comma (,) as the column delimiter.
Next on the Advanced pane give each column to appropriate name, data type, and length. For specific details on matching SQL Server (and other RDBMS) data types to those used by SSIS see the mapping table in the documentation.
You'll also want to confirm the columns are sent to columns of the appropriate data type which can be done on the destination component editor in the Data Flow Task.

General Page:

Columns Page: