I'm looking for direction here. I need to automate a process that will import several different file types into SQL Server. They are all text based files (no excel) but the internal format of the files is different. Some are comma delimited, others are tab delimited. They all contain the same base information (customer name, address, phone, etc.) but some files contain additional data. I want to only import the common data from each file. So, some data will be skipped over in one file but in another file all the data needs to be imported.
I was going to use an SSIS ForEach loop but I'm having trouble figuring out how to handle multiple file formats. I get stuck on the connection string for setting up how to import the files. Has anyone come across this before? If so, can you push me in the right direction?