flat file connection

Question

flat file connection

ronan.healy

SSCrazy

Points: 2232
More actions
September 19, 2014 at 7:08 am

#299599

hi
I was wondering when setting up a flat file connection. is there a way to set it up so that if a different amount of columns come in in files at different time can the package auto correct it without me having to touch the package. and also if there is blank columns in the file is there a way we can discard them so they don't get pick up.
thanks in advance

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

g.britton SSChampion Points: 13863 More actions · Answer 1

How would the package recognize that the for has fewer or more columns? If it's obvious you can use a conditional split. The idea is to have the flat file input insert the whole line into the data flow as a single column the use CS to send it to the right path depending on what it finds or perhaps by a parameter passed to the package at runtime.

Gerald Britton, Pluralsight courses

ronan.healy SSCrazy Points: 2232 More actions · Answer 2

well at the moment when I set up the file I have set the number of columns in file.

so example the flat file I read in have 5 columns and 5 columns header. as long as every time the package picks up the file and it has 5 columns it runs fine. but then the format of the files changes by client request. they now have 6 columns is there anyway I can get it to know there is an extra columns and fix it so it runs.

the package will be automated so when it goes live if they have extra columns in it even by mistake the packages wont run. I want to know if there is a way around this so the package will always run and load the data

g.britton SSChampion Points: 13863 More actions · Answer 3

Use a CS or script transform to check the format. Don't assign columns in the flat file connector.

Gerald Britton, Pluralsight courses

ronan.healy SSCrazy Points: 2232 More actions · Answer 4

and if I used that and it found an extra column what do I do then to make it read the dat from the extra column into my package.

also even if I don't name the coulmns doesn't it then come in as column0 column1 columns 2 etc which is like naming a column

g.britton SSChampion Points: 13863 More actions · Answer 5

Well, what DO you want to do with extra columns? Ignore them, process them specially? This is really a business question, I think. What do your customers (even if just another internal department or person) expect to happen?

The idea is this (I've used it many times):

1. The Flat File connection just outputs one column. Call it 'Data'.

2. The CS transform or Script transform (or both) examines the one column and decides what to do: Accept it and send it into the dataflow or reject it and send it to the error output. Note that the script component could set a variable or add a RecordType column to the data flow that a CS transform could use for further discrimination. That's one reason you may use both transforms in series.

3. If accepted, add Derived Column transformations to parse the one column 'Data' into the other columns you need. Note that, if you use a CS transform, you can have one path for "normal" inputs and one for inputs with extra columns.

4. Further process the data flow(s) and finally output the data somewhere (e.g. OLEDB destination)

Gerald Britton, Pluralsight courses