How to manage different input (Excel files) format

Hi all,

I have created a package which import data from excel file and do some technical & business validation on the data. My package has about 20 control flow items. Now I'm asked to handle a second (and probably more in the future) excel file format (columns name are different, some fields are murged in one single column...).

I definitely don't want to create a different package for each excel file format. But I can't find a way in the control flow to execute a particular DataFlow in one case and another DataFlow in other cases. Typically I would like to evaluate an expression an depending on the result execute a DataFlow or another one. Even in a given DataFlow I cant find a way to have a condition and process different Excel Source depending on an expression result. Or it would be good if I could say to my Excel Source to discover the columns name and types at runtime and let me manage the columns manually in the data flow. Is that possible ? I know SSIS manage metadata on the columns based on the data source is there any way to manage the metadata manually ? I coulnd't find anything about that in BOL.

I guess an easy workaround is to have a different package just to import the different excel files in a common staging table and each package calls a single package which contains all technical & business validation.

Any help will be appreciated.

Kind regards,

Sbastien.

Have you discovered the expressions on precedence constraints? They seems like ideal fit for your requirements.

Double click a precedence constraint line, select a condition and an expression.|||

Right! That's what I needed.

Thanks for your answer.

How to make input columns unavailable for downstream components?

Hi,

In a SSIS Data flow task, whatever you are doing with the input columns that come from a Data source like a Flat File Source, these columns are always visible and available as input columns for all Transformation and Destination components in the Data flow.

Our Custom Component is a column mapping component that transforms many input columns into many oputput columns and we would like that the used input columns are not available anymore to the downstream components...

Is that possible?

I saw that with the Unpivot component it is possible to make some input columns unavailable to downstream components...So I think there is a way to do the same in a custom component...

Thanks for any help,

David

David-Paris wrote:

Hi,

In a SSIS Data flow task, whatever you are doing with the input columns that come from a Data source like a Flat File Source, these columns are always visible and available as input columns for all Transformation and Destination components in the Data flow.

Our Custom Component is a column mapping component that transforms many input columns into many oputput columns and we would like that the used input columns are not available anymore to the downstream components...

Is that possible?

I saw that with the Unpivot component it is possible to make some input columns unavailable to downstream components...So I think there is a way to do the same in a custom component...

Thanks for any help,

David

If the component you are building is a synchronous component then the rows will be available downstream. That is simply inherent in the nature of the data-flow.

Unpivot is an asynchronous component (just like Merge Join, Union All, Sort etc...). You can think of asynchronous as meaning that the "shape" of the data changes when it goes through the component (that's not really what it means but for simplicity - it works).

You can make your custom component asynchronous if you want but there isn't much point - it will degrade performance.

-Jamie