2

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?

Improve this question
4
  • I would import the text as "one column" into a staging table and then process it from there using T-SQL. How do you identify the different "formats" ?
    – Mister Magoo
    Jun 19, 2014 at 18:30
  • I was thinking about using a .net service to rename all the file extensions to .txt, import to a single column staging table (thank you), then processing with TSQL. I get these files from multiple vendors so I could technically specifically write an SSIS package for each type of file but I will have hundres of different vendors and it would be a nightmare to manage.
    – Aaron Hurst
    Jun 19, 2014 at 18:52
  • You could try running queries against the files to only pull the common data elements out. You'd need to have some mechanism for identifying the separator beforehand and change the connection string appropriately. See also stackoverflow.com/questions/8168548/…
    – billinkc
    Jun 20, 2014 at 0:56
  • Does this have to be done via SSIS?
    – Solomon Rutzky
    Jun 20, 2014 at 19:22

2 Answers 2

Reset to default
2

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.

Improve this answer
2
  • Typically, each vendor has it's own file format. I'll have hundreds of formats.
    – Aaron Hurst
    Jun 19, 2014 at 19:10
  • Ouch. Then you have an integration challenge, not a data challenge. I recommend writing a separate application for each vendor which outputs to a common format; if the formats are very similar, you can try branching within a single app. Either way, your SSIS package or other ETL process need only concatenate the various clean files and import once.
    – Jon of All Trades
    Jun 19, 2014 at 19:27
0

If header names are same across all the delimited files, then you can use Script Task/Script component inside Foreach Loop Container. You can put the logic ( by hard-coding header names) - First check file's delimited type (Comma/Tab etc) and then extract data for only those columns you are interested from that flat file.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.