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

Question

Hi,

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. 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>

Obviously this is driving me mad, as it should be so simply and straightforward, so any help would be very greatly appreciated!!!

Pat.

Answer 1

Hi Pat,

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.

HTH, Gerald

Answer 2

Hi Pat, I had excactly the same problem. I spend more than a day banging my head over it. Here is what I found. Although configuration file allow 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><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 value of x000A. That is because the file I configure Flat File Connection with at design time has {LF} as a row delimeter. Had I point to a file that have {CR}{LF} row delimiter, then ColumnDelimiter of the last column would be x000D_x000A_ and these are not overriten with values from config file.
In my case if I desinged package with file that have {CR}{LF} as a row delimiter than at run time file that have {LF} delimiter is not being loaded into a table at all (obviosly, because it is unable to find {CR}{LF} which indicates the end of the row). However, if I design package poining to the file fith {LF} delimiter then at run time file with {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 souce file.

Solution: I end up poining at design time to the file with {LF} delimiter and adding Derived Column transfomation task after Flat File Source with Expression to remove {CR} in case when file at run time has {CR}{LF} delimiter:

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

Hope this helps.