0

I have excel files that follow the same table structure, but varying file names and sheetnames (usually due to dates). I can not use script task. I have succesfully followed the top example here that loads files despite sheetname and file name. However, I encountered a new error, the example works when the column names are in the first row, but when they all start in a different row, thus requiring a sql command from variable, the example does not work.

The variable I use is "SELECT * FROM [" + @[User::SheetName] +"A9:AB99999]" which equates to SELECT * FROM [Bdx-Nov$A9:AB999999]. I follow every step from example exactly, but when using this variable opposed to just sheetname variable package returns following error:

An OLE DB record is available. Source: "Microsoft Access Database Engine" Hresult: 0x80004005 Description: "The Microsoft Access database engine could not find the object ''Bdx-Nov$'A9:AB99999'. Make sure the object exists and that you spell its name and the path name correctly. If ''Bdx-Nov$'A9:AB99999' is not a local object, check your network connection or contact the server administrator.". Error: 0xC020204A at Data Flow Task - Load File, Excel Source [14]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.

But previewing with the sql command variable does work, any insight to resolve this?

SQL_Noob
  • 107
  • 6
  • If you update the Excel Connection Manager and the Dataflow's `DelayValidation` property from False, to True, does it make any difference? – billinkc Jan 25 '23 at 16:52
  • Unfortunately no @billinkc – SQL_Noob Jan 25 '23 at 16:58
  • If it works in preview and not during execution my first guess would be a varying path or missing access to the file. If you perform a preview, your privileges and local drives are used. If you execute the package on a server, the path is searched on the server and another user might be used (at least if the package is executed via job or similar)... at least this was the reason in similar issues I faced in the past. – Tyron78 Jan 28 '23 at 15:05
  • @Tyron78, Thanks for the response, however this was not run on a server (but will need to be) when failing. I found a solution and will post shortly. – SQL_Noob Jan 30 '23 at 19:21

1 Answers1

1

I still have not figured out the root cause of the error, but a solution that works with the above linked guide is to use table variable opposed to SQL command from variable.

So rather than create a variable that looks like so: SELECT * FROM Sheet1$A9:AG I instead created a variable like so: @[User::SheetName] + "A9:AG" = Sheet1$A9:AG

The original problem was sql command from variable would preview in excel source but package execution would return error stating it can not find such a sheet. Now with table variable, preview and package execution work.

SQL_Noob
  • 107
  • 6