September 28, 2015 at 7:48 am
We are using SQL Server 2012 Data tools and trying to read data from a flat file with <LF> as header row delimiter and Row Delimiter. '~' is used as a Column delimiter.
The package gets hung when we receive <CR><LF> as part of data.
On the Flat file source the on error and on truncation property is set to "Ignore Failure".
If I update the property to "Redirect rows". then the package fails.
However, with the same connection manager and flat file source settings, I am able to process the file, If I use a package developed in BIDS (SQL SERVER 2008 ).
Can someone help me understand, If its a bug in sql server 2012, or if there is an approach to process the file.
September 30, 2015 at 2:04 am
sudhirnune (9/28/2015)
We are using SQL Server 2012 Data tools and trying to read data from a flat file with <LF> as header row delimiter and Row Delimiter. '~' is used as a Column delimiter.The package gets hung when we receive <CR><LF> as part of data.
On the Flat file source the on error and on truncation property is set to "Ignore Failure".
If I update the property to "Redirect rows". then the package fails.
However, with the same connection manager and flat file source settings, I am able to process the file, If I use a package developed in BIDS (SQL SERVER 2008 ).
Can someone help me understand, If its a bug in sql server 2012, or if there is an approach to process the file.
Unfortunately, I had no chance working with SSIS 2012. But I had the same problem with data file. Some data lines contain <LF> as row delimiter, others contain <CR><LF>. I implemented a component script in SSIS. This component reads the data file and check if <LF> is existing, it replace with <CR><LF> and write data to new data file.
string rowDelimiter = GetRowDelimiter(Dts.Variables["ConnectionStringFile"].Value.ToString());
Dts.Variables["RowDelimiter"].Value = rowDelimiter;
if (rowDelimiter == "{CR}{LF}") return;
try
{
//StreamReader sr = new StreamReader(sourceFile);
StreamReader sr = EncodingDetector.GetStreamReaderWithEncoding(sourceFile);
string newPath = Path.GetDirectoryName(sourceFile) + @"\" + Path.GetFileNameWithoutExtension(sourceFile) + "NewDelimiter" + Path.GetExtension(sourceFile);
StreamWriter sw = new StreamWriter(newPath, false, Encoding.UTF8);
string str = sr.ReadLine();
while (str != null)
{
if (rowDelimiter == "{CR}")
sw.WriteLine(str.Replace("\r", "\r"));
if (rowDelimiter == "{LF}")
sw.WriteLine(str.Replace("", "\r"));
str = sr.ReadLine();
}
Dts.Variables["ConnectionStringFile"].Value = newPath;
sr.Close();
sw.Close();
}
catch
{ }
The purpose of function GetRowDelimiter is to read the file and detect row delimiter
If rowDelimiter == "{CR}{LF}": file does not contain <LF>
Else
Replace <LF> with <CR><LF>
Thanks,
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply