3

I have recently started a project with sensors that add data to a single CSV file as data the data. I would like to have this data stored in a SQL Server database in a relatively real-time manner. I know that CSV files can be imported but I am looking for a method that will sync data between the file and the database such that data automatically gets imported to the database as it is added to the CSV file.

I have looked around and found this post which suggests using SSIS to accomplish this. Having never used SSIS, I am not sure if it is the best method or if it even meets my requirements. Does someone have a good suggestion for how I can go about doing this and/or good resources for how to do this basic task in SSIS.

If it is relevant, I am using Microsoft SQL Server 2014 Standard edition on a Windows 7 desktop.

  • 1
    I don't use SQL Server, but I know what I'd do under the servers that I do use. I'd have an event that fired every [1-5]mins(^) - merged the data in the .csv (as an external table) with a "proper" database table. I'd also cron a script to truncate (from the top rather than the bottom) the file every [1-5] mins so that there is an absolute minimum of records in the .csv. Use an UPSERT or MERGE or whatever the SQL Server syntax is. (^) Depends on your definition of "real-time". – Vérace Mar 29 '16 at 0:57
  • What version of SQL Server are you running? Would a polling solution work for your needs like @Vérace suggested? Are you looking to maintain duplicate data in your database and CSV file? Can you intercept the data stream that is feeding the CSV file so it can write to both the CSV and the database at the same time? – Erik Mar 29 '16 at 1:19
  • I'm running 2014 version and I cannot interrupt the data stream. A polling solution might work (every 1-5 minutes would be acceptable). I cannot intercept the data stream though. – BenP1192 Mar 29 '16 at 1:27
  • 2
    I would stay clear from doing this at all costs. Wait until it can be architected properly, otherwise this is going to have constant issues. Does the sensor data insert process to the CSV take OS level locks? Will those be compatible with SQL Server for an openrowset/datasource? What happens when the CSV data gets so large that loading the data takes longer that what is considered "real time"? Basically, either architect it from the beginning or consider a new definition to "real time". – Sean Gallardy Mar 29 '16 at 1:49
  • Sean's right - can you engineer is so that the sensors can write to SQL instead (you can use BCP to write out the csv file if it must exist!)? - best to avoid 'OS' files for this style of project. If you need a quick fix - you can use Linked servers to access a csv file (mssqltips.com/sqlservertip/2018/…). Maybe you could set a scheduled job to create a copy of the file every x mins and then 'Linked server' to the copy?? Either way, not really recommended. – MHSQLDBA Mar 29 '16 at 11:15
1

I have a partial answer - according to Microsoft's documentation it should work but I have not tested it. As I understand it you have a CSV file which is regularly updated, and you want the data to be available in a SQL Server 2014 database.

SQL Server 2012 onwards supports FILETABLE - this is a special type of table that maps onto folders and files in the file system. Each row in the filetable corresponds to an actual file, and updates to the physical files are reflected by updates to the rows in the filetable, and vice versa. See https://msdn.microsoft.com/en-us/library/ff929144(v=sql.120).aspx that describes FILETABLE. This Microsoft article further states that external to SQL Server, the filetable appears as a file share with files and directories. Another article https://technet.microsoft.com/en-us/library/gg492083(v=sql.110).aspx states you can use Windows explorer, or a command prompt to move and copy files into the filetable.

So assuming this works, you could create a SQL Server filetable that maps to the CSV file you want to load. You still have the problem of extracting the data from the filetable into a usable form in SQL Server. To do this, create a database view that retrieves the row from the filetable, converts it to a varchar(max), then uses a SQL Server split function with APPLY to split the string at line boundaries, then at column boundaries. A split function is discussed here https://stackoverflow.com/questions/20512855/split-function-by-comma-in-sql-server-2008 . SQL Server 2016 has an inbuilt string split function, although you are not using that version.

The result should be you have a database view, which gets the current state of the CSV file from the filetable (file system) and converts it to tabular form. By using the view you should always have an up to date version of the data from the CSV file.

DIsclaimer: As stated I have not tested this, so feel free to amend or delete if it's known to be incorrect.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.