May 29, 2008 at 6:46 am
Hey fellow SSIS junkies,
I have a little bit of a headache on this one.
Case scenario:
a) On a specific target setting system, excel templates were sent out for each area, where the header starts at the third row
b) Within the header, each branch belonging to the specific area is set
c) No two areas have the same amount of branches (for example, in a demographically larger area, there may be 16 areas as opposed to a smaller area which has 12 branches)
d) The assignment handed to me is to take this information and to upload to SQL using SSIS with the power of the for each file enumerator.
Now, I am extremely familiar with for each file enumerators, and could set up an excel connection manager where I change the connection string dynamically
What I am struggling to come to terms with is how to dynamically set the column headings. Once more, each branch is unique to an area, therefore, the column heading would be unique. Also, the width of the file would be dynamic.
Any suggestions as to the best approach?
I am very familiar with excel VBA macros as well, and am reaaaally tempted to write a macro which splits out the template into smaller branch files. But this doesnt gel so well, I should be able to do this in SSIS directly.
Any help on this will be appreciated. Possibly scripting tasks?
~PD
May 29, 2008 at 7:23 am
SSIS does not handle column definitions changing well (or really at all). As you may have noticed, every component in your data flow has the definition of it's inputs and outputs. These cannot really change at runtime without errors.
You need to get the workbooks into a standard set of columns or create mutiple connection managers.
For getting them into a standard set of columns, you could use a script task as a data flow source component and use the excel object model to read the contents of the workbooks. This would actually be pretty easy to do and if you are familiar with VB.Net scripting, it would not be a bad approach. In addition to this approach, a more elegant approach may be to create a custom SSIS connection manager or Data Flow source component that would do the same thing.
Since you do have a fixed set of formats, creating multiple connection managers is not as bad as it sounds. You could come up with some way to determine the type of file you are dealing with and then call another SSIS package specific to the file type. In the sub-package, open the file and stream it into a standard format and then your main package could take this result and process it. This would mean that you have to create a sub package per file type, but you could keep it all pretty dynamic this way without having to write much code.
May 29, 2008 at 7:31 am
Thanks Michael,
After giving some thought, I am going with the scripting task, as this is a once off kind of assignment where most probably the only person who cares about what technique used is myself.
a) Basically, I will still use the for each file enumerator to get every area file.
b) I will have a scripting task which takes control of the Excel object model. This will fire a loop which will create a branch file with their respective totals. This scripting task will handle standardizing the format on the file.
c) I will have a secondary for each file enumerator which will import data in a far more classic SSIS kind of way.
Thanks once again! Dont you just love getting involved in things after the horse has bolted, and the barnyard door is broken....
Sigh, the life of a data artisan (as MS now calls us)
~PD
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply