Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS Data Migration Job Expand / Collapse
Author
Message
Posted Friday, July 05, 2013 3:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 02, 2013 7:06 AM
Points: 15, Visits: 136
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
Post #1470638
Posted Friday, July 05, 2013 3:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:35 AM
Points: 12,206, Visits: 9,169
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1470644
Posted Friday, July 05, 2013 4:00 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 12:24 PM
Points: 4,828, Visits: 11,183
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.




Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1470648
Posted Friday, July 05, 2013 7:48 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, April 07, 2014 2:09 PM
Points: 79, Visits: 242
You can try the solution listed here.

SSIS: How to load multiple excel files into multiple SQL tables?


Vikash Kumar Singh || www.singhvikash.in
Post #1470740
Posted Friday, July 05, 2013 8:26 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 12:24 PM
Points: 4,828, Visits: 11,183
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1470760
Posted Tuesday, July 09, 2013 2:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 02, 2013 7:06 AM
Points: 15, Visits: 136
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
Post #1471475
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse