SSAS Tabular - SSDT 2017 Get Data - multiple flat files (csv/txt) from a folder
There are manay examples of new using modern Get Data feature while connecting to SQL Server. However, I can't find any examples of importing data from multiple flat files (csv/txt) located in one folder. How should I make an initial connection to the data source? Whether it should be a connection to a folder or to one of the files? How should I buid the query chain (query M). It seems that the way I do it in Excel does not work. I would be gratefull for any tips.
In SSDT I did it as I used to do it in Excel PowerQuery and Power BI.
Created connection to a folder (Get Data -> File -> Folder).
Within appeared Query Editor window I created a new query to csv/txt file (Query -> New Query -> Blank Query) with the help of functions Csv.Document/File.Contents.
Turned a file path to a parameter and created a function - fxOneFile.
Used this function to multiple files within the folder (step 1) - query ManyFiles.
Everything as usual as it would be in Excel and Power BI, except for the query Table which represents the initial connection to the folder. Query ManyFiles represents multiple files in one data set after appling custom function fxOneFile.
But then I click Home ->Close & Apply get the following error:
"Failed to save modifications to the server. Error returned: 'Expression in partition 'Partition' in table 'ManyFiles' references an unknown entity".
When I return to Query Editor, the list of queries looks differently - no grouping folders anymore, query ManyFiles has gone, Table turned to Table(2) and parameters FilePath and FolderPath turned to queries returning scalar values.
Please give me a hint, what is this error with partitions during processing.
Why the initial query structure is not saved?
Are these bugs of maybe I miss something.
Thank you in advance for your suggestions.
BT Thakks commented
Hey I was wondering if you get that work by any chance? I am looking for the same as solution.