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.
asked May 10, 2011 at 01:51 AM in Default
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.
answered May 10, 2011 at 03:02 AM
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...
answered May 10, 2011 at 11:26 PM