none
SSIS package always drops last row of .csv file import

    Question

  • We have the an issue with our .dtsx packages developed in BIDS 2008 and deployed on SQL Server 2008 R2.that import data from .csv flat file connections.

    We do not have the issue in our DEV environment hinting that the problem may be environmental.  The DEV server version is 

    Microsoft SQL Server 2008 R2 (RTM) - 10.50.1790.0 (X64) Apr 22 2011 11:55:34 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (Hypervisor)

    PROD does not have the Cumulative 4 Pack on yet and it is on that server where the last record of the .csv is skipped.. We will be putting on the cu soon, but it is not a known issue fixed in this KB.

    PROD version: Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (Hypervisor)

    The only other environmental difference is the 64-bit version of the Office Tool Kit was installed on the PROD server, but not DEV

    The import files are .csv and there are no apparent anomalies in the column delimiters or designated row identifier (even for the last row). When running one package as part of a job from the SQL Agent, the last record is skipped every time. Text qualifier is set to none. We are baffled.

    Anyone with a resolution or work around?

    Wednesday, July 13, 2011 1:47 PM

Answers

All replies

  • I guess you tried to process the same file in DEV and it works.

    Can we see the end of the file how it looks like?

    I think you overlooked something and it is not a product related issue.

    I am also sure you get a warning message but do not capture it because you have no logging implemented in that package, right, yes?

    If so, I suggest the following approach:

    • Go add extra good logging to the package and if you get a warning post it here

    Arthur My Blog
    By: TwitterButtons.com
    Wednesday, July 13, 2011 1:58 PM
    Moderator
  • Arthur,

     

    You are correct, we attempted to import the same file in DEV and there was no issue.

    We will add logging to the package in production and let you know the warning that we capture.

     

    The last record of files have CR LF characters like all the other rows and the cursor is on the next blank row when viewed in a text editor. 

    Wednesday, July 13, 2011 2:24 PM
  • Arthur,

    I implemented full logging on one of the packages with the issue and it did pick up a warning on the production system as well as failing to import hte last record of the .csv file.

    The last record was imported in the same package in our DEV environment using the same import .csv file.

     The warning entry in the log from PROD:

    OnWarning ST1C8MEZ11 AM\EPC_REP_JOBACCT Import MEI Trade Spend LE Fixed {180311ec-13ea-40ce-9ddf-dd6248333183} {BF625F0A-43B7-4CBA-A12B-357AE41F7F0F} 7/14/2011 7:40 7/14/2011 7:40 -2145378289 0x There is a partial row at the end of the file. 

    I do not see th partial row at the end of the file when viewing in a text editor.  Also, the package successfully imports all rows using SQL Agent in DEV, but not from the SQL Agent on PROD.

    The last row of data in the import file is:

    201107,10040,HTR,201109,2011,0,7.27 with CR LF at the end.

     

    Thursday, July 14, 2011 12:59 PM
  • Arthur,

    Addtional log entries that I did not include in the previous post:

    There is a partial row at the end of the file.

    The total number of data rows processed for file ".......\Trade Spend LE Upload Templates\Fixed Spend Template.csv" is 1728.

    The total number of data rows processed for file "........\Trade Spend LE\Trade Spend LE Upload Templates\Fixed Spend Template.csv" is 1728.

    A component has returned from its PrimeOutput call. : 1 : Import Trade Spend LE Fixed Template
    Rows were provided to a data flow component as input. :  : 142 : Flat File Source Output : 136 : Data Conversion : 137 : Data Conversion Input : 1727
    Rows were provided to a data flow component as input. :  : 189 : Data Conversion Output : 172 : OLE DB Destination : 185 : OLE DB Destination Input : 1727

    The package processed 1728 records, but next two component only received 1727 records to process.

    Again, complete investigation of the template file did not indicate there was a partial row.  The file successfully imports under identical conditions using the package in a job on our DEV server that has cumulative pack 4 installed.  Production does nto. 

    The only other difference between the environments is that Production has the 64-bit Office Share Features installed,  DEV does not have this additional feature installed.

    Friday, July 15, 2011 12:46 PM
  • Someone else had also troubles with the last line:

    http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/e51b775a-8601-491b-8ad4-a71b0f0d59ce

    Maybe his solution also works for you?


    MCTS, MCITP - Please mark posts as answered where appropriate.
    Answer #1: Have you tried turning it off and on again?
    Answer #2: It depends...
    • Marked as answer by Eileen Zhao Monday, July 25, 2011 3:14 PM
    Friday, July 15, 2011 12:54 PM