Import data from flat files to sql server

ali_alannah
ali_alannah used Ask the Experts™
on
What the best solution for the following scenario

I have a table 
Create table  trns (
Sp_name nvarchar (150),
Amount decimal (12,3),
Acc_no nvarchar(14),
Bank_Id tinyint ,
Trn_Date date 
)

Every month i have to import a data from different 3 data sources 
( .txt , .mdb , .xls ) and load it in this table.... Note i dont delete the old data from this table but i ADD the new data to it
All data source has a different column names , different column numbers .... But all have a columns i need to import it in the above columns
So what the best option i have to use to perform this task  and to AUTOMATE it .... Is using SSIS and  save it and give it to the end user to use it every month to imort the data ? ..........or using tsql like bulk insert and make small a .net application to let the user use it ......
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
SSIS will do all that and allow you to redirect invalid or corrupt rows . This single feature is one reason I will always recommend SSIS when doing ETL work even when neither the source or the destination are MSSQL
i suggest you to create SSIS packacge adn schedulit

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial