Using BIDS that came with SQL Server 2008 R2, I created a text file import package. I used files generated from my local machine for local testing. However, I find that these files get their format changed after being SFTP'd to the data warehouse.
Not being able to visually see a problem with the text file to be imported, I copied it down to my local machine and compared to one generated at my machine. Not until I chose "Show All Characters" in Notepad++ did I see the difference: my local file ends its
lines with a carriage return-linefeed character, whereas the file that went through SFTP ends its lines with a linefeed character only.
I suspect there's something very basic going on here that I don't know. I won't ask anybody to explain it, but I'm wondering if there's some way I can import both file formats. The column numbers and lengths are identical, it's the just line
ending character that's different and causing zero rows to get imported. The only other thing I know to do is recreate my flat file connection, and I haven't even tried that yet.
View Complete Post