SSIS::플랫 파일 가져오기 에러.

플랫 파일을 Table로 저장 해서 가져오기를 할 때 특이한 값이 가져오거나, 일부 행을 가져오지 못 할경우가 발생한다. 
ssis 버그라고 하는데 SQL R2 버전이면서 win7 x64일 경우 발생한다.


아래는 버그에 대한 설명이고 sp2를 설치하면 된다고 한다.
혹은 플랫 파일 연결자의 속성중  TextQualifier  를 아무것도 없이 지운다. 

http://support.microsoft.com/kb/2576118 
 

here is a bug in SSIS when exporting data from SQL to a comma delimited text file where you specified no text qualifier.

This bug apparently only occurs when you develop the SSIS on a x64 win7 PC and copy the .dtsx file (windows explorer copy/paste) to network path of a x86 SQL server and schedule the job to run from SQL Agent on the same x86 SQL server.

When the SSIS runs, the text file is written out containing text qualifier = "“_x003C_none_x003E".

If you look at " _x003C_none_x003E", it actually means <none>.  x003C = "<" and x003E = ">".

 

If you go into the SSIS package, double-click in the connection manager section to open the flat file connection manager editor and try to clear the text qualifier removing the <none>, the <none> value get added back in.

The only work-around is to NOT open the flat file connection manager editor, but instead make the change using the property window and clear out any value in the TextQualifier field.

 

Other similar problems occur when you actually want to put a real value in the text qualifier.  For explain if you select the double-quote as the text qualifier and copy to a x86 server and run, you end up with a text file containing the value " _x0022" around each field instead of a double quote.

 

In my mind this is a serious bug, I did some research and other people have been having this same issue.

 

FYI, the SQL server is currently SQL2008 with SP2.  I will try to get it updated to SP3/4 this weekend to see that will help.

 

FYI2, when I am developing the SSIS using VS2008 and my local test/run is done through visual studio on my Dev PC (x64).  Everything works fine this way.  I do not have a instance of SQL on my machine so I did not test it running from SQL Agent on my PC.