CODEWITHSUNDEEP
sql-server-2008ssis
Ben Thul
Ben Thul

Reputation: 32677

Dynamic column list for SSIS file load

I have been given a file that is broken into sections. Sections are delimited by "start-of-<sectionName>" and "end-of-<sectionname>" lines. The two sections that I'm concerned about are a list of fields and the actual data. Here's a brief example:

start-of-fields
a
b
c
end-of-fields
start-of-data
1|2|3
2|3|4
end-of-data

My problem comes in where a given file might not have all of the fields. What fields the file does have are listed in the fields section. Is there a way in a Script Component in the data flow task to parse out what columns are present and then parse the data section accordingly? I currently have a working Script Component (C#) that ignores the fields section and is assuming that all of the columns are there. I noticed in the parent class to my task's output buffer that there's a member called BufferColumnIndexes, but it's protected so I can't get at it from my script.

Upvotes: 0

Views: 316

Answers (1)

Edmund Schweppe
Edmund Schweppe

Reputation: 5132

Certainly, a script component can read the input file line-by-line. When it found the start-of-fields line, it could build up a nice little array of column headers, stopping when it reached the end-of-fields line. Then it could read each line between the start-of-data and end-of-data lines, parse out the values, and add a row to an output buffer with the provided values and whatever you want for defaults.

However, that's an awful lot of stateful coding. Since you can't actually debug script components in SSIS 2008, getting it to work will be a real pain.

You might well be better off writing a full-blown custom data flow component. Or even writing a completely separate console application.

Upvotes: 1

Related Questions