Hi. I am pretty new to SQL 2005/2008 and have a question relate to importing a text file to temp table in SQL 2005. I have a text file with '|' separator and up to 10 columns in each line. Some lines will have 10 columns, but some will less than 10. When I tried to import the file to SQL table, I am either getting truncation error. If I tried to ignore truncation error, I see that line with less than 10 columns are combined with next time as one record. Does anyone have any idea how I can import this text file properly? Example: Import file will have: SUM|1|DINING|5278.54|-249.57|508.54|-22.03|5|-13.98|3 DSC|1|5|PT 100% FD|-103.40|15 DSC|1|6|VIP 100% FD|-33.97|3 SVC|1|100|Charge Tip|508.54|61 TND|1|1|Cash|1|1630.31|62 SUM|2|LOUNGE|5281.54|-154.31|533.41|-60.97|7|-10.49|2 SQL Table will show: SUM 1 DINING 5278.54 -249.57 508.54 -22.03 5 -13.98 3 DSC 1 5 PT 100% FD -103.4 15DSC 1 6 VIP 100% FD -33.97|3 SVC 1 100 Charge Tip 508.54 61TND 1 1 Cash 11|1630.31|62 SUM 2 LOUNGE 5281.54 -154.31 533.41 -60.97 7 -10.49 2

Check this: http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/8dbe1852-c17b-4971-8a91-e9b570340aceThanks http://dwhanalytics.wordpress.com/

Thanks. IT WORKS! Using the link, I was able to import the file. Though, I am wondering there has to be a better solution than using the script code.

Hi John-Oxford, Please refer to the following link, http://www.codeproject.com/KB/database/SSIS_Integration.aspx Hope it helps. Thanks, Eileen