Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rongrace
Starting Member
13 Posts |
 Posted - 2012-02-20 : 06:01:50
|
| I posted a query a while back regarding automating the import of multiple flat files in to a database. The solution I was given was to use a FOREACH loop as per the followinghttp://www.sqlis.com/sqlis/post/Looping-over-files-with-the-Foreach-Loop.aspxI've now actualy got around to doing the work and when I follow the example it appears to cater for multiple flat files loading in to the same dataset.What I actually wanted was as followsI have a number of flat file e.g AjDetails.Txt, AjTotal.Txt, AjDescs.Txt etc. All of the files have different layouts. What I want to be able to do is automate the import of these files in to seperate tables so AjDetails.Txt creates a table called AjDetails and so on. If you follow the example given above in the Url is it just a matter of creating another variable to hold the dataset name i need to createI hope this makes sense |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-20 : 11:24:57
|
| if metadata of files is changing then you need to use separate data flow tasks for doing the transfer. Otherwise you need to use script task to create object for flat file connection dynamically and refresh metadata and do the transfer------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
rongrace
Starting Member
13 Posts |
Posted - 2012-02-22 : 03:25:17
|
| There are over 200 files, so I'd have to create a task for each one, that seems a bit long winded. Is there no way of including a variable for the table in the way you include a variable for the file name |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-22 : 09:41:07
|
| then only other option is to use a loop in script task to create an object for flat file task each time and then refresh metadata inside that------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Importing data using a FOREACH loop