Hi before we add our new acquisition's machines to our network we will temporarily read their dimensional and fact excel files from a share and ingest them into our warehouse.
our dba told us in a meeting yesterday that rather than staging them the usual way from ssis, a linked server itself can bypass any such etl app and read the excel directly.
Is that true? how is that done? i'm reluctant anyway but promised him i'd check it out.
September 4, 2024 at 1:33 pm
it is but not advisable - a Excel linked server is 1 per file per tab - not something I would setup.
if your DBA is ok with installing the MS ACE driver then a better option would likely be openquery/openrowset to query the files directly from a SP - see https://www.mssqltips.com/sqlservertip/6178/read-excel-file-in-sql-server-with-openrowset-or-opendatasource/
September 5, 2024 at 11:27 am
thx frederico, one important question before i ruin his day...what if there are multiple files all having say the customer tab? i think i understand the rest of your answer in that the product tab would require a separate linked server etc etc. we probably have close to 10 tabs between facts and dims.
Just confirmed and my original reply was incorrect - it is one per file - but note that you can't change the file name on the fly, any change to it would require a new linked server to be setup.
see https://www.excel-sql-server.com/excel-import-to-sql-server-using-linked-servers.htm for instructions to setup. MOSTLY important is giving permissions to the service account TEMP folder to the users executing the code even though the link states its only for 32bit version, this is incorrect - 64bit also needs it.
but really this is not something I would do or recomend - using the openrowset/opendatasource allows for the same access to the files and is fully configurable by using dynamic sql to allow any of the components to change on the fly.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply