Best way to upload files into different database tables

Hi Everyone,

I am begineer to the SSIS package, can anyone help me to resolve this apporach.

I have a different files with different extension like tbla.txt,tblb.txt,tblc.xls and tbls.csv in a same folder. I would like to upload these files into different database tables. for example tbla.txt should upload to tbla table, tblb.txt file should upload to tblb table and so on. I have used flat file source to OLE DB destination asa Dataflow for the each file. It works well. However if the new file is added to the folder again i need to modify the pacakge to include another dataflow.

I dont think it is a best approach, Can anyone have a best approach to implement this?

Thanks,

Karthik

When you say "However if the new file is added to the folder again i need to modify the pacakge to include another dataflow." do you mean the new file is:

1. Another .txt file with the same name (ie, tblb.txt) but different columns

2. Another .txt file with the same name (ie, tblb.txt) and the same columns

3. Another .txt file with a different name (ie, newfile.txt) and different columns

4. Another .txt file with a different name (ie, newfile.txt) and the same columns as the previous file

If you can let me know how what the differences are between the two files (name and/or columns) then I may be able to answer better.  Assuming the columns are the same on both the files but the names are different that is a pretty easy fix with a ForEach Loop.

Thanks for the quick turnaround.

Yes the columns are different in the files.

Another .txt file with a different name (ie, newfile.txt) and different columns.

Thanks,

Karthik

Unfortunately the data flow in SSIS is very rigid.  If your columns on the file change then your data flow will break.  You are going to have to set up a different data flow for each different file.  There would be some ways around this, but that would involve a bunch of .Net code that I would not be able to provide insight into. 

If there are two files that are going to be having the same columns but different names, then that would be someting that is easily handled inside SSIS, but not different columns.