March 21, 2012 at 2:37 pm
My goal is to load data from an particular tab in an excel 2003 workbook into sql server 2008 r2 standard edition using SSIS. I am wondering if SSIS can go to a particular tab in the excel workbook, save all the data from that tab into a text file? The SSIS package when then take the data from the text file and use sql to load the data into sql server. If this is possible, can you tell me and/or point me to a reference that would show me how to accomplish this goal?
The following is the problem I am currently having with the excel file received from the customer:
1. column headings are not compatible with sql server,
2. lots of columns are hidden, and
3. and there are links in the excel that point to references that are worthless by the time I get the workbook from the customer.
March 21, 2012 at 5:53 pm
1. How are the headings incompatible?
There are options to skip header rows which may help.
2. From memory the driver doesn't care too much whether a column is hidden, it selects the data from a range or named range regardless of visibility.
3. Not sure how you mean this one. if the result is text it will come in as nvarchar.
Here's a few references to get you started:
Example:
http://sql-bi-dev.blogspot.com.au/2010/08/dynamic-ssis-package-to-import-excel.html
How to select a range from a particular sheet:
http://sqlserversolutions.blogspot.com.au/2009/02/selecting-excel-range-in-ssis.html
Be aware of unicode conversions:
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply