July 5, 2013 at 3:32 am
Hello
We have multiple Excel Files (over 300) - each with different column names, all containing at least 1 row of data.
I need to find a way to be able to insert the data within the excel files into a SQL tables. (The name of the excel file is the same name as the SQL table)
I would ideally like to do this using some kind of loop rather than set up 300 different data flows. This job will be executed many times for many different target DBs.
So far we are looking at doing this 2 different ways,
To use MS Access to copy the data, but my colleague is saying that using excel as the source will cause data type issues. or
To use SSIS to loop through the excel files and copy the data. My issue around this is the column mappings will change for each excel file.
Can anyone recommend a solution?
Ideally the source should be kept in an Excel format as they need to be easily editable by our Business Analysts before the transfer, we have columns highlighted in different colours so the analysts can identify which ones they may need to change.
Any pointers, different methods or suggestions etc would be greatly appreciated.
Many thanks in advance
David
July 5, 2013 at 3:52 am
SSIS cannot handle changing metadata, at least not out-of-the-box.
And SSIS has issues with the data types as well (actually it is the ACE OLE DB provider which gives the issues).
I would look into scripting with .NET as an alternative.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 5, 2013 at 4:00 am
Can anyone recommend a solution?
If you can enforce a common column-naming / data-typing format on all of the Excel files, you have a chance of using SSIS and looping to do this relatively easily. It still won't be easy - largely because Excel does not enforce data types.
But if you allow them flexibility, your solution needs to code around that flexibility - a lot of work - and SSIS is probably not the right tool.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 5, 2013 at 7:48 am
You can try the solution listed here.
SSIS: How to load multiple excel files into multiple SQL tables?
Vikash Kumar Singh || www.singhvikash.in
July 5, 2013 at 8:26 am
Vikash Kumar Singh (7/5/2013)
You can try the solution listed here.SSIS: How to load multiple excel files into multiple SQL tables?
Although it looks like this will work, SSIS is not really adding anything here - might as well keep the whole thing in T-SQL if following this route.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 9, 2013 at 2:26 am
Thank you all for your responses, they were very useful to us. We think we have a way to accomplish what we need using Access, with linked tables.
Dave
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply