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?
Best Answer
How many is "several"? If it's three or four, I'd start by writing a simple .NET script in SSIS which sorts out the files into separate directories. Use several foreach loops, each working in its own directory, with the files in that directory having a consistent format.
If there are a lot of formats, it'd probably be faster to write a little application which parses files and writes a single clean file for subsequent BCP or SSIS import.