Sometimes missing source columns in Data Flow Task

Question

Sometimes missing source columns in Data Flow Task

smac

SSC Eights!

Points: 855
More actions
May 16, 2011 at 1:56 pm

#240402

I have noticed my data flow task bombs out when the input file sometimes lacks 3 columns. Am I missing a basic 101 step or do I really have to create 2 different task flows dependant upon whether I have 35 columns or 38?
Thanks!!

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

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

SSIS can't easily accommodate this sort of requirement. If it's only ever one of two formats, you'll just need two data sources and some conditional logic to force processing down the right path...

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

smac SSC Eights! Points: 855 More actions · Answer 2

That's what I thought.

Can I put the 2 datasources and the controlling logic in the data tab of 1 SSIS package or do I need to have 2 seperate SSIS packages?

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

You can do it in a single package - but you'll need 2 dataflows and some precedence logic to control which is executed. You may also have to set delayvalidation to true to avoid metadata errors.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

smac SSC Eights! Points: 855 More actions · Answer 4

This might be a long route but this what I have done and seems to work:

Step 1: Script Task - Read in text file & count the header row if count = 35 put file into folder 35 if count = 38 put file in folder 38.

Step 2: Script Task - Do files exists in folder 35 if so set variable fileExistsResult35 = true, also do files exists in folder 38 if so set variable fileExistsResult38 = true.

Step 3: Created Precedence Constraints based on above variables to execute packages that import 35 columns or 38 columns. If fileExistsResult35 = false doesn't execute pkg 1 but = true exec pkg 1, same for fileExistsResult38 = false doesn't execute pkg 2 but = true exec pkg 2.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 5

This might be a long route ...

I'm afraid it is a bit 🙂 Nonetheless, glad you got there in the end!

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

COZYROC One Orange Chip Points: 28499 More actions · Answer 6

The solution you have above is not very easy to maintain. If you have a file with another set of input columns, then you have to include another data flow to handle it.

If you can use third-party solutions, check the commercial CozyRoc Data Flow Task Plus. It allows handling of variable number of columns at runtime. The case you have described above can be handled with only one data flow.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/