January 29, 2013 at 6:01 am
Hi all, wondering if someone could please assist?
I need to import data from an Excel (.xls) spreadsheet into a specific table in my database via SSIS and save the package so that it can be run when required but I've no experience using SSIS (yet!).
The spreadsheet and data is saved to the same server (64bit) as the SQL Server 2008 R2 instance that holds my database but I'm really struggling to build the SSIS import package and what I've found on the net isn't aimed at complete SSIS beginners (i.e. press this to do this!) and I'm getting quite frustrated at my own lack of knowledge 🙁
I'm the sole DBA for my company and don't have anyone I can bounce ideas off of so I'd really appreciate some assistance from 'those in the know'.
Many thanks, M.
email: info@weekendwebdesign.co.uk
Personal Website: http://markallen.co.uk/
Business Website: https://www.weekendwebdesign.co.uk
January 29, 2013 at 6:06 am
The stairways section, goes from basic up to detailed information on SSIS http://www.sqlservercentral.com/stairway/72494/
What you want seems pretty basic.
You need 2 connection managers, 1 Excel which points to the xls file, 1 ADO.NET OLEDB depending on your requirements pointing to your SQL server
Then you need a data flow task, drag in a excel source, drag in a ADO.NET OLEDB destination depending on what you used as the connection manager. Then drag the flow line between them
Configure the source and destination as you need to, ensure mappings etc are all the same, then your pretty much good to go for importing that 1 xls file into the database.
January 29, 2013 at 6:14 am
Many thanks for your quick reply, I'll have a look at the stairway section. 🙂
email: info@weekendwebdesign.co.uk
Personal Website: http://markallen.co.uk/
Business Website: https://www.weekendwebdesign.co.uk
January 29, 2013 at 6:30 am
Possibly a silly question but can I use the SSMS import/export wizard to copy the data from the .xls doc into a specific table within my database and save as an SSIS package?
email: info@weekendwebdesign.co.uk
Personal Website: http://markallen.co.uk/
Business Website: https://www.weekendwebdesign.co.uk
January 29, 2013 at 6:32 am
Absolutly yes. One of the last options is to save the package to the MSDB database or the File system.
January 29, 2013 at 7:12 am
Shameless plug: there are quite some issues when dealing with Excel and SSIS. This article describes some of them:
What’s the deal with Excel & SSIS?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 29, 2013 at 7:14 am
Cracked it!!!! Used the import wizard and it worked first time. 😀
I've saved the SSIS package so I'll take a look at that later after I've applied the update to the production server. I'll also be continuing to read the stairway series on SSIS as it'll no doubt come up in my next MS certification exam!!
Many thanks again. M.
email: info@weekendwebdesign.co.uk
Personal Website: http://markallen.co.uk/
Business Website: https://www.weekendwebdesign.co.uk
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply