We run into this problem time and time again, and just want to put out the blog and set the record straight.
BizTalk nsoftware SFTP adapter v2 does not have an option to change the encoding of the file. The only possible way that I can see if to upgrade to v3 and use the "After Get" to simulate a FTP command to change the encoding of the file. For another project, we used a sort of "hack" to ftp to and from an internal location to change the encoding, but that only made sense because it's a monthly file.
We are working on HL7 project. The files come in by the thousands per day and they are all transactional, for example, the refill orders must depend on the original order. The standard HL7 ORM schema calls for <CR><LF>, but the input file we got only has <LF>, which you can see in Notepad++.
I created the following into a stored procedure so that I can run it again and again:
DECLARE @dynsqlcmd VARCHAR(250)
DECLARE @tblname VARCHAR(150)
DECLARE @synname VARCHAR(150)
DECLARE @cnter INT
-- Drop all synonyms
DECLARE dbcursor CURSOR FOR
SELECT sysobjects.name AS 'table name'
FROM sysobjects
WHERE sysobjects.type = 'sn'
OPEN dbcursor
FETCH NEXT
FROM dbcursor
INTO @synname
WHILE (@@FETCH_STATUS =0)
BEGIN
SET @dynsqlcmd = 'DROP SYNONYM ' + @synname
EXEC (@dynsqlcmd)
FETCH NEXT
FROM dbcursor
INTO @synname
END
CLOSE dbcursor
DEALLOCATE dbcursor
-- Create Synonym
DECLARE tblnmcursor CURSOR FOR
SELECT sysobjects.name AS 'table name'
FROM CMSQNXTTESTSQL.PlanData_Dev.dbo.sysobjects
WHERE sysobjects.name <> 'dtproperties'
AND sysobjects.name <> 'sysdiagrams'
AND sysobjects.type IN ('u','v')-- No SPs
OPEN tblnmcursor
FETCH NEXT
FROM tblnmcursor
INTO @tblname
WHILE (@@FETCH_STATUS =0)
BEGIN
SET @synname = 'Any Prefix' + @tblname
SET @dynsqlcmd = 'CREATE SYNONYM ' + @synname + ' FOR [Server Name].[Database Name].dbo.' + @tblname
EXEC (@dynsqlcmd) --PRINT (@dynsqlcmd) --
FETCH NEXT
FROM tblnmcursor
INTO @tblname
END
CLOSE tblnmcursor
DEALLOCATE tblnmcursor