CSV file with extra header and footer lines

Naomi Nosonovsky 8,146 Reputation points
2022-07-06T15:23:12.52+00:00

Hi everybody,

Please don't make a comment about CSV being a host for the problems :( We receive a csv file which is normal CSV but it has a header row in the format 10datetime and the footer row in the format of 90countofrows. I'm trying to read this file as ragged right in order to get the header and footer rows correctly. So for the trailer I set 2 columns Record Type and RecordCount which is delimited with CRLF and output width is 4000 (in reality is much less).

So I look at the file in Notepad++, it looks OK and it is supposed to be 11,381 details rows. All looks ok in Notepad++, however in SSIS when I try to preview and skip first 11300 rows it still shows the rows, I try skipping more and it still shows and I cannot figure out why and how to get this trailer row correctly.

Is there another way to get this row properly (using any other format or changing it)?

Actually, I take it back. It shows OK in preview and even seems to be working on the first try, but then it stops working and not loading the trailer row anymore. What should I change to make it work reliably?

Thanks in advance,

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,470 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,644 questions
0 comments No comments
{count} votes

Accepted answer
  1. Tom Phillips 17,741 Reputation points
    2022-07-06T15:47:24.697+00:00

    I am not exactly clear on what you have configured. But the "preview" option in SSIS is very simple. It does not work with complex use cases. Just because Preview doesn't work, does not mean it is not configured correctly.

    FYI, although it is possible to read this type of file in SSIS, I always read these files into "stage" tables with columns defined as the main rows, and then use TSQL to parse the header/footer out of the data. It is much easier to code and to deal with in TSQL.


2 additional answers

Sort by: Most helpful
  1. Naomi Nosonovsky 8,146 Reputation points
    2022-07-06T16:22:53.433+00:00

    I just tried loading this file from Visual Studio directly. It worked 2 times out of 3, so something is not 100% reliable, but I cannot figure out what. The other strange thing with that dtsx that it "remembers" very old configuration setting :( The table doesn't even exist anymore, but when I look in the dtsx with Notepad++ I see some old table reference in the package. How can I clean it from old memory?

    UPDATE. I just loaded this file into our Stage environment fine (all 11,381 rows were loaded correctly). Now trying to load into Production using original package.

    What would make the package work unreliably, do you have ideas? Stage is SQL 2019, production is SQL 2016

    Microsoft SQL Server 2016 (SP2-CU15-GDR) (KB4583461) - 13.0.5865.1 (X64)
    Oct 31 2020 02:43:57
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: )

    Looks like it's an older version but it's not my position to ask for updates, right?

    0 comments No comments

  2. Naomi Nosonovsky 8,146 Reputation points
    2022-07-06T16:53:39.183+00:00

    So the change I did is to change output of the second column to 500 characters instead of 4000. I loaded the file twice in Stage using new package correctly and it didn't load properly using original package.

    The other thing is that I'm failing package using raiserror(message, 16, 1) and this code is supposed to get executed. My package is called by the main package and this is executed as SQL Agent Job. However, the job succeeds and failing the package using raiserror seems to be doing nothing. Do you know of a better way to fail? I know that in some of the packages we use a script task to fail it, but why RAISERROR doesn't achieve the same result?

    Thanks.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.