How would you tackle having to import various flat files into SQL, the file format COULD change but is unlikely and more files may need to be added to the process. My thinking is to use SSIS but the person who started this project previously was writing it using command lines. All ideas greatfully received.
asked Apr 20, 2011 at 06:33 AM in Default
SSIS is the way to go IMO.
Here at work we have some sort of dynamic SSIS implementation, where the control data is stored in sql server, allowing new file formats to be easily added without having to re-deploy the SSIS package for each additional file format.
I'm not too sure how it was done (before my time, and SSIS is pretty much an unknown for me), but it is very flexible and fast to the bargain.
answered Apr 20, 2011 at 06:42 AM
If you know, that the source file format may change in the future, but the destination tables will remain the same, I go by SSIS, because in that case, when source file changes, you only update the SSIS by updating the flat file source and add appropriate transformation to meet the destination tables.
answered Apr 20, 2011 at 06:45 AM
Deep down I know the answer is SSIS...
If it's likely to change, you probably should take that into acount and write the import process in a language that can deal with it appropriately. I'd use PowerShell.
answered Apr 20, 2011 at 10:54 AM
Grant Fritchey ♦♦
I'm going to join with everyone else and say that if the file format is any one of the standard types (CSV, TSV, fixed width) that SSIS is the way to go. And this remains true even if you think the format might change, then you just update the SSIS packet.
I would however look outside of SSIS if the file format is genuinely nonstandard or for some reason significant preprocessing before being imported. I have for instance dealt with situations where I just needed to extract the e-mail addresses from otherwise unorganized text, or where I needed to strip out unusual characters such as an ASCII null character or bell character before importing, or where numerous lines needed to be split into header and then value to be turned into a single row in the database.
In all those cases, I turned to a more flexible programming language (normally Python, but just about any of the major ones would work).
answered Apr 20, 2011 at 03:16 PM