0

I have a CSV file where there is a header row and data rows in the same file.

I want to get information from both rows during the same load.

What is the easiest way to do this?

i.e File Example - Import.CSV

2,11-Jul-2011
Mr,Bob,Smith,1-Jan-1984
Ms,Jane,Doe,23-Apr-1981

In the first row, there a a count of the number of rows and the date of transmission.

In the second and subsequent rows is the actual data, in this Title, FirstName, LastName, Birthdate

GordyII
  • 7,067
  • 16
  • 51
  • 69

3 Answers3

0

SQL Server Integration Services Conditional Split Transformation should do it.

Stuart Ainsworth
  • 12,792
  • 41
  • 46
0

I wonder what would You do with that info in the pipeline. However, there is only one solution to read it in one pass (take a look at notes/limitations at the end):

Create a data flow

One path from condition split will be the first row of file (mycounter=0) and the other path will be the rest of the rows (2 in your example).

Note#1: file source can set only one metadata for each column in the source. This means that if your first column of data is string (Mr, Ms, ...) then You have to set it as string data type in the source. Otherwise, if You set it as integer (DT_Ix) it will fail as soon as it encounters row with string data (Mr, Ms, ...) in the first column of file. This applies to all columns, not just the first one.

Note #2: SSIS will see only the number of columns You told it to. This means that You have to have the same number of columns in EACH row. Otherwise, You have ragged csv file and You need to take another approach - search the Internet. But those solutions also require different layout of csv.

Community
  • 1
  • 1
Filip Popović
  • 2,637
  • 3
  • 18
  • 18
0

Answers in the following links explain how to load parent-child data from a flat file into an SQL Server database when both parent and child rows exist in the same file next to each other.

How do I split flat file data and load into parent-child tables in database?

How to load a flat file with header and detail data into a database using SSIS package?

Community
  • 1
  • 1