February 18, 2009 at 1:47 pm
Hi,
I have 40 different flat files which has to be imported to different tables. I am trying to use SSIS package. Each flat file is different in structure(i.e. no.of.columns and width of coulmn), hence the table will have different structure. Will I be able to do this using foreach loop container. If I use foreach loop container, the connection manager for each file has to be different for do I configure each file????
Thanks
February 18, 2009 at 7:42 pm
You can use a for each loop to bring back each file name. You will need a step to determine what table a file needs to be loaded to. Does the file name indicate that?
You could then call a data flow task for that file format and destination table. In other words, you will have a data flow task for each file format and destination table combination.
February 18, 2009 at 7:52 pm
You will also have a separate connection manager for each file format.
This is the most straightforward approach but tedious to code.
February 18, 2009 at 9:39 pm
40 connection managers - I do not envy that at all :doze:
But the alternative is to script it all and that is not nice either!
There are so many different files there that it might be worth building an 'Import' table (or tables) in SQL Server that contains all of the meta data required for the input files, their structures and the output tables and then use that in a script task. Not a quick job either way, but once your script job is built it will easily accommodate future changes (eg additional files or file format changes).
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 18, 2009 at 9:55 pm
If the input files are delimited and have column headers, the scripting might be easier.
I have an application where we receive Excel files from several sources. None of them are the same format and the format may vary some from month to month. In Excel, you can loop through the columns to see how many there are and what the column headers are. Then build a query that selects the columns into a standard layout.
I don't know if you can do something like that with delimited files. With fixed-width files, the scripting would be much messier and hard to maintain if the file format changes.
February 18, 2009 at 10:05 pm
kbatta (2/18/2009)
---I don't know if you can do something like that with delimited files.
---
You certainly can - and more easily than with Excel, I think.
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 19, 2009 at 8:36 am
Thank you all,
I would like to use the script task, but i dont know how to do that. Can someone give me a sample code to import a flat file to sql server table.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply