Home Forums SQL Server 2008 SQL Server 2008 - General SSIS Conditional Split not working when run on SQL 2008 R2 using SQL Server Agent RE: SSIS Conditional Split not working when run on SQL 2008 R2 using SQL Server Agent

  • I figured out the problem: There were double quotes enclosing the tested fields in the data source, but not other fields. 2008R2 appears to pass the quote marks down from the data source whereas 2008 does not. The Conditional Split was not getting matches becuase of the quote marks.

    I determined this by trying the package from BIDS on the 2008R2 server itself rather than my workstation. I could then see what was going on in the data flow using data viewers.

    It appears that 2008R2 handles text delimiters differently. We had another problem that I fixed before posting this one: We originally had the target files' text delimiters set to nothing (blank). After upgrading to 2008R2, the destination files started showing "_x003C_none_x003E_" as the text delimiter. This problem was common enough for me to find a quick solution on the Web. I assume that the issue with the source file is similar. The source has no text delimiters, but three of the fields are encased in double quotes. From what I can tell, 2008 dropped the quotes around those fields, but 2008R2 (correctly) considers them part of the field contents since they are not delimitiers.

    I ultimately fixed the problem by using REPLACE to replace the double quotes with empty strings before the Conditional Split. I could have just changed the conditions, but then the package would work from the SQL Agent but not from my workstation.

    Thanks, Koen, for the help. I was trying to think of how I could login to SSIS using the SQL Agent account when it occured to me to try BIDS from the server.