CODEWITHSUNDEEP
sql-serverssis
Disco
Disco

Reputation: 31

Why am I getting 0 rows processed in SSIS?

I am creating a SSIS package using MS Visual Studio 2012 Shell with .Net framework of 4.6.01055. The SSIS package has a Data Flow task with Flat File Source, Data Source Row count, Final Data Set count and OleDb destination tasks. It connects to a SQL Database and I have checked to see that my connection has been tested.
I have a flatfile connection manager which picks up a text file. On the Preview section it only shows the header columns in the flat file connection manager editor. The error message is only at warning level with the following message: [Flat File Source [10]] Warning: The end of the data file was reached while reading header rows. Make sure the header row delimiter and the number of header rows to skip are correct. The file itself has a total of 19 rows with the first being the header row.

I have spaces in the header names of the origin file. So on that file I edited to have no spaces on the final column. That did not cure the issue. The last column is a date but I am designating as OutputColumnWidth of 50 and datatype as string[DT_STR]. I have the Row delimiter as {CR}{LF}. I have the column delimiter as {|}. When run the package file name does not change.

In the General section of the editor under locale = English; Unicode is not checked; Code Page = 1252 (ANSI-Latin1); Format = Delimited; Text qualifier = none; Header row delimeter = {CR}{LF} (I have tried just CR or LF as well); Header rows to skip=0 (I have tried 1 as well since there is only one header row); and I have checked Column Names if the first data row.

Why am I not getting data in my preview section? And why is it thinking I only have a header?

Upvotes: 3

Views: 2657

Answers (3)

Fer R
Fer R

Reputation: 149

I found the issue is the encoding of the source flat files. These files came from UNIX / Linux systems. Of course you can modify the attribute by using some software like Notepad++, but if you want to automate this, you will need something else. So I wrote a Script Task which takes each source file, converts it to UTF8 (my target DB uses 1252 codepage) and codepage to match, and generates a new file with some prefix in the name. Then the SSIS process takes the result files and it works well.

To convert the files I'm using this kind of C# code:

                using (TextFieldParser csvParser = new TextFieldParser(fullSourceFileName))
                {
                    csvParser.HasFieldsEnclosedInQuotes = true;

                    // Uncomment to skip the row with the column names
                    // csvParser.ReadLine();

                    while (!csvParser.EndOfData)
                    {
                        // Read current line fields, pointer moves to the next line.
                        string unicodeLine = csvParser.ReadLine();
                        var encondedBytes = Encoding.UTF8.GetBytes(unicodeLine);
                        var strUTF8 = new string(encondedBytes.Select(b => (char)b).ToArray());

                        builderOutput.AppendLine(strUTF8);
                        rowCount++;
                    }
                }

TextFieldParser class is available in Microsoft.VisualBasic.FileIO library, you'll add a reference to it.

Upvotes: 0

NotImplementedException
NotImplementedException

Reputation: 448

Just to add to the other answer: I had the same problem, when i opened the file in notepad, it became clear that there was a trailing empty line at the bottom.

So: make sure the last line of the file actually contains text.

Upvotes: 0

DeanOC
DeanOC

Reputation: 7262

It seems to me that your text file does not have a matching EOL marker, and so SSIS never splits the lines (and treats the file as just having one big header).

Try opening the file in a text editor that lets you see the EOL marker. I know that NotePad++ can do this for you.

NotePad++ will also let you change the file's encoding as well, in case that is also a problem.

NB: The problem could also be that you are not specifying a correct column delimiter. If the delimiter you specify in SSIS doesn't match characters in the file, then SSIS will also think that you have a single header row where everything is in the first column.

Upvotes: 1

Related Questions