asked
SSIS - Dynamic Flat File Source
I have a project that needs to import data from various sources. I have created a table which shows the source type e.g. flat file, csv, xls etc and the path and file names etc for each source. I have created an SSIS package that will use the information in this table and using a For Each loop will loop through and find the relevant Path and Filename for each source and populate a variable from it. My challenge is to create the flat file connection in the For Each loop for each file from the variable created in the previous step and telling it how long the columns should be etc. Should I set up a tables that relates to the source and shows the field name and the length of each column and then somehow link into that but I really have now got stuck. Any ideas greatfully received, please let me know if you need more information. Many thanks
In case the flat files will have the same structure (all excels will have the same structure, all csv will have the same structure, then you can create several data flow tasks for each file type and then by the constraints execute only the particular data flow in the for each loop container. If the files have a different structure, then the only possibility will be the script component where you can write custom processing based on the configuration tables. But it will work only as long as the output will be the same. I mean you will have to have fixed output columns and only write custom processing of the source files.
Many thanks, we have different source format and different destination format so it's looking less and less possible.
You said there are different source formats and different destination formats, but are they fixed, i.e. do the same source file formats always go to the same destination format(s)? To expand further, do you have say 3 different text file formats always go to 3 different respective formats, txtFormat1 always goes to txtFormat1, txtFormat2 always goes to txtFormat2, excelFormat1 always goes to excelFormat2 etc. If the above is the case, then you can specify a dataflow for each type of format and control the flow using constraints depending on the variable value (As Pavel said). Also, you wouldn't need to store the format structure in the database, just what type of structure it is. The down side to this is if there is a new file structure, then a new data flow and constraint would have to be created. This has me interested, let me know if I can help...
+1 @Daniel Ross, only to add, that it is manageable only for a few format and structures. Otherwise the amount of data flows will not be manageable as there will be a very big amount of data flows. In case of 3 input formats and each having 3 possible structures you need 9 data flows. Not counting possible different destinations (I mean destinations with different structure). In case the number of possible combinations is relatively small, I go this way as I and you described. Eventually in higher count of combinations I would go with separate package for the combination and and a control package which then depending on the configuration calls particular processing package.
you are right @pavel, there would be a point where the effort outways the reward. I'm sure there would be a way around it though.
