Integration services importing flat file

[Programmer99]

I need to import a flat file into a SQL 2005 database table. The text file has fixed lengths so I defined all these lengths and thought I was good to go. When I previewed it, I noticed the file has carriage return lines so it was messing all the formatting up. My question is how to get around this? I tried just added 2 characters to the last field but it did not work and I really just want to ignore the line feed characters. I can get the 2000 version of DTS to work but we are moving everything over to SQL 2005 and need to do it through SSIS integration. Please help.

 

[mrdenny]

When you define the connection to the text file one of the options should be the line ending character. Select the correct LF, CR, LFCR option.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Programmer99]

I tried all of those options and it still did not make a difference. I should be able just to switch that option instead of having to reconnect to the file and reestablish that it is a LF correct?

 

[mrdenny]

Correct, you should just be able to change the option.

Is the file created by a unix system? You may need to find a copy of unix2dos.exe and have it conver the file before importing it.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com