Importing from an Excel Spreadsheet

Question

Post reply

Importing from an Excel Spreadsheet

latingntlman

SSCarpal Tunnel

Points: 4348
More actions
January 30, 2009 at 9:26 am

#127702

Hello all, I've gotta import daily counts from an Excel file that someone manually maintains into a table that will be called by a sproc that an RS report will use. The report is pretty much done except for that piece of info that I need to bring in from the Excel file.
My question is mainly based on the fact that I haven't dabbled with SSIS at all. What kind of process should I put in place for this daily import?
For those who may suggest to add a datasource from Excel to the RS report, I've already discarded that option b/c the data for the prior day gets updated by 10am every day, so if anyone runs the report before that they may not get desired results. Besides, I think that it should be part of nightly job that updates the table.
Need some input from our experts.
thx,
John

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

You need to do the following steps

1. Add 1 Data Flow Task

2. Inside that Data Flow, Add 1 Excel Source & 1 OLE DB Destionation

3. Set Source & Destination Connections

4. Execute the Package

--Ramesh

latingntlman SSCarpal Tunnel Points: 4348 More actions · Answer 2

Thanks for the info. Unfortunately I need to know how to get to step 1. Do I connect to integration svcs from sql mgmt studio, or do I use visual studio, then what?

COZYROC One Orange Chip Points: 28499 More actions · Answer 3

latingntlman (1/30/2009)
Hello all, I've gotta import daily counts from an Excel file that someone manually maintains into a table that will be called by a sproc that an RS report will use. The report is pretty much done except for that piece of info that I need to bring in from the Excel file.
My question is mainly based on the fact that I haven't dabbled with SSIS at all. What kind of process should I put in place for this daily import?
For those who may suggest to add a datasource from Excel to the RS report, I've already discarded that option b/c the data for the prior day gets updated by 10am every day, so if anyone runs the report before that they may not get desired results. Besides, I think that it should be part of nightly job that updates the table.
Need some input from our experts.
thx,
John

John,

You can create OLEDB connection based on Microsoft Jet OLEDB provider to your Excel workbook. You can then use SQL alike statements to retrieve data from particular worksheet. Check also these links for more information about working with Excel in SSIS:

http://dougbert.com/blogs/dougbert/archive/2008/06/16/excel-in-integration-services-part-1-of-3-connections-and-components.aspx

http://dougbert.com/blogs/dougbert/archive/2008/06/18/excel-in-integration-services-part-2-of-3-tables-and-data-types.aspx

http://dougbert.com/blogs/dougbert/archive/2008/06/21/excel-in-integration-services-part-3-of-3-issues-and-alternatives.aspx

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

COZYROC One Orange Chip Points: 28499 More actions · Answer 4

latingntlman (1/30/2009)
Thanks for the info. Unfortunately I need to know how to get to step 1. Do I connect to integration svcs from sql mgmt studio, or do I use visual studio, then what?

You design your SSIS packages in Visual Studio (BIDS). It looks like your knowledge of SSIS is extremely limited. I would suggest you'd better go and grab a book about it. These are good ones:

http://www.amazon.com/Microsoft-Server-2005-Integration-Services/dp/0672327813/ref=pd_bbs_sr_8?ie=UTF8&s=books&qid=1233338523&sr=8-8

http://www.amazon.com/Expert-Server-Integration-Services-Programmer/dp/0470134119/ref=pd_bbs_sr_1?ie=UTF8&s=books&qid=1233338523&sr=8-1

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

latingntlman SSCarpal Tunnel Points: 4348 More actions · Answer 5

Thanks CozyRoc. You're right; my knowledge of ssis is limited. Although I used to create dts packages in sql 2000.

I'll definitely follow up on your links and info.

John

Jay_Noob SSCommitted Points: 1565 More actions · Answer 6

latingntlman,

An FYI that made me nuts the first time I had to import Excel to SQL. Excel uses Unicode data types for text fields, so in the SQL table set your data types as nvarchar, nchar. Otherwise, you will have to explicitly convert the text fields to non-unicode data types in your SSIS package.

*see "Data Conversion" Data Flow Transformation task in the previously mentioned books