subramani.rudrappa 78855 (11/16/2012)
Hey Hi,I know we can use SSIS to get data from excel and load in to SQL table from where i can achieve requirements.
I thought of using only SQL to get data from excel and achieve the requirements so that we need not to go for SSIS.
If you are importing data on a routine basis as part of a inter-application data transfer, then SSIS is a good choice.
For one-off ad hoc importing, however, I much prefer to use OPENROWSET() queries. There are few fiddly bits to get right or the process fails:
- The spreadsheet cannot be open while you run the import.
- You must know the name and path of the spreadsheet and the name of the worksheet you want to import and hard-code them into your query (unless you resort to dynamic SQL).
- You have to enable ad hoc distributed queries on your server.
- If you have mixed data types in the same column, the type conversion can fail if the process guesses wrong about how to type the column. Google for "Excel IMEX=1" for more information and a work-around.
Once you get all that working once, it's easy to re-do for other uses. I don't know what "best practice" is considered here, but I do a lot of one-time imports from Excel, and this is my favorite tool in the box.
Rich