SSIS - How to set 'RowDelimiter' and 'HeaderRowDelimiter' from XML configuration file?

I want to be able to configure the 'RowDelimiter' and 'HeaderRowDelimiter' values used by my Flat File Connection Manager using my XML configuration file.

I've used XML config files with SSIS packages many times without any problems, and so I know my config file is correctly formatted and is being picked up by my package, but the package just doesn't seem to be altering the 'RowDelimiter' values as specified in the config file.

Basically I want to be able to configure the use of either {CR}{LF} line terminators, or {LF} terminators, but I can't understand what I'm doing wrong. 开发者_如何学Go No matter what values I try to configure, the values given at design time seem to take precedence. I've also tried specifying nothing at all for the delimiters at design time, but then the process fails due to it ignoring the actual terminators in the flat-file (i.e. again it seems to completely ignore my configuration settings and attempts to use exactly what was specified at design time).

My config entries are as follows:

<Configuration ConfiguredType="Property" 
 Path="\Package.Connections[Connection 1].Properties[HeaderRowDelimiter]" 
 ValueType="String" 
 xml:space="preserve">
    <ConfiguredValue>_x000D__x000A_</ConfiguredValue>
</Configuration>

With SSIS 2008 R2 I can change the row delimiter using expressions. The trick is to use the values "{CR}{LF}" resp. "{LF}" for setting the desired row delimiter.

Although the configuration file allows you to specify HeaderRowDelimiter, each column's delimiter is still stored in the package itself and these values are not configurable! The problem is specifically with the delimiter of the last column being saved in the package code like this:

<DTS:FlatFileColumn>
    <DTS:Property DTS:Name="ColumnType">Delimited</DTS:Property>
    <DTS:Property DTS:Name="ColumnDelimiter" xml:space="preserve">_x000A_</DTS:Property> <!-- this is the one! -->
    <DTS:Property DTS:Name="ColumnWidth">0</DTS:Property><DTS:Property DTS:Name="MaximumWidth">50</DTS:Property>
    <DTS:Property DTS:Name="DataType">129</DTS:Property>
    <DTS:Property DTS:Name="DataPrecision">0</DTS:Property>
    <DTS:Property DTS:Name="DataScale">0</DTS:Property>
    <DTS:Property DTS:Name="TextQualified">-1</DTS:Property>
    <DTS:Property DTS:Name="ObjectName">ExpirationDate</DTS:Property>
    <DTS:Property DTS:Name="DTSID">{C6321083-8C75-43C7-B8C8-B234F7C645BA}</DTS:Property>
    <DTS:Property DTS:Name="Description"></DTS:Property>
    <DTS:Property DTS:Name="CreationName"></DTS:Property>
</DTS:FlatFileColumn>

Note how ColumnDelimiter of the last column has a value of _x000A_. That is because the file I configured the Flat File Connection with at design time has {LF} as a row delimiter. Had I pointed to a file that has {CR}{LF} as a row delimiter, then the ColumnDelimiter of the last column would be _x000D__x000A_ and these are not overwritten with values from config file.

In my case if I designed a package with a file that has {CR}{LF} as a row delimiter, then at run time the file that has a {LF} delimiter is not being loaded into a table at all (obviously, because it is unable to find {CR}{LF} which indicates the end of the row). However, if I design a package pointing to the file with the {LF} delimiter, then at run time the file with the {CR}{LF] delimiter gets processed, but {CR} is appended to the last field of the file. In my case it was a date field and having {CR} character at the end invalidates it completely. Just try this:

print isdate('1/1/2010' + char(13))

You can use Replace() and replace char(13) with '', but this means adding instance specific SQL task which will stop working in case let say another column is added to the source file.

I end up pointing at design time to the file with the {LF} delimiter and adding a Derived Column transformation task after the Flat File Source with an Expression to remove {CR} in case the file at run time had a {CR}{LF} delimiter:

REPLACE(ExpirationDate,"\r","")