0

Problem:
I receive multiple sets of flat files on a weekly basis that need t be imported my Database. The flat files I receive are not in a conventional format to import, so they need to be run through a script and be parsed in to a more SQL friendly format. These flat files are usually in JSON, TXT, XML, LOG, ect.

Current Solutions
Current I have a windows forms application and another GUI to transform the files and to bulkimport to SQL tables. However, I'm finding it unreliable to ask users to import data, and I would much rather automate the tasks.

More recently, I have been creating SSIS packages. This proves to much faster and useful since I can add script components. It allows me to manually parse whatever flat files I throw at it. My issue is finding a way to automate this. I have no control of the server where my database is hosted. So I'm unable to deploy the packages there to bring in the files. Currently, I'm just running the packages on my local machine manually to get the data in.

Needed Solution
I need a way for me to automate getting these flat files in. Originally I wanted to request and FTP server for the files to be dumped in. Then the files would be picked up by my packages and imported into the SQL Server DB. However, since I have no control of any of the local folders on that server, it seems to be impossible for me to automate this. Is there a better way for me to find a solution for this? Could I build something custom in C#, Python, Powershell, etc.? I'm very new to the scene and trying to find a solution for this problem has been a nightmare.

AlanPear
  • 737
  • 1
  • 11
  • 32
  • What I understood is you don't have access to database server and that is why you are not able to put your files? – Prayag15 Feb 10 '18 at 18:51
  • What version of SQL Server? Considering that you state that the data format differs, is at least the data contained within consistent (same definitions, columns/nodes, etc). If not, you're going to have a very hard time. Lack of any consistency means automation is going to be limited or not at all. – Thom A Feb 10 '18 at 19:36
  • @Prayag15 from my understanding once a package is deployed it can only deal with files on that local machine. I don't have access to anything outside of connect to that machine DB via SSMS. – AlanPear Feb 10 '18 at 19:49
  • @Larnu I'm using SQL Server 2016. The data I'm dealing with is output from hardware configuration. Each project is fair consistent. One might be all JSON, another might be all XML. The only time I need to change anything is if it's a new generation of hardware or a new Manufacturer. – AlanPear Feb 10 '18 at 19:50
  • 1
    SSIS can access any files that the service account, or user running the package, has access to. That can be on the server itself, on the local machine of the person using SSMS, or even a completely different location. The key thing is ensuring you use UNC paths, and drive mappings. – Thom A Feb 10 '18 at 20:00
  • 1
    If you're using SQL Server 2016, this puts you in a good position; they introduced [JSON data in SQL Server](https://learn.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server) that edition. SSIS has no native JSON reader (at the time of this comment). Personally, I would load the files you have as raw text to a staging table on your SQL Server, using SSIS, and then parse the XML/JSON on the data engine. Without any sample data though, there's little more I can offer on that route. – Thom A Feb 10 '18 at 20:05
  • 1
    @Alanpear , you can design your package in such a way that it picks the files from your local machine and push the data to server machine where the database is located. Next you can schedule these package from windows task scheduler. You can check below link for the reference. https://stackoverflow.com/questions/8079616/how-to-schedule-a-ssis-package-job-in-sql-sheduler-or-windows-scheduler – Prayag15 Feb 10 '18 at 20:05
  • Thank you so much for these answers. As for automating it on my own machine, I think I may run into two issues. One is storage. It's minor cause all im dealing is flat files. And two, my machine either needs to be on, or I need to be at work so the packages can run. Also, is bring the file in raw to sql server faster? When I parsed the JSON files through SSIS (about 120 files) it took around 10-15 mins. Lastly, if one you can type up a more detailed answer with links, I can mark it as the answer to my question. thanks guys – AlanPear Feb 10 '18 at 21:31
  • @AlanPear i think you need to work with `FileSystemWatcher` so when a file is added you can execute the package related https://www.codeproject.com/Articles/26528/C-Application-to-Watch-a-File-or-Directory-using-F – Hadi Feb 11 '18 at 18:12
  • @Hadi but how would this work as a dataflow? All the files would need to be passed through my personal computer. Can you post another example that would show this as a real life situation? – AlanPear Feb 11 '18 at 18:41
  • @AlanPear `FileSystemWatcher` is a library in the .Net Framework, you can build a service that will listen to a folder (watcher) when new files are added you choose to run a `dtexec` command. I think this approach it what you need but you have to search for how to build a folder watcher service – Hadi Feb 11 '18 at 18:51
  • Right, but this would need to be on a single client machine? Such as my own for example. It seems like a huge bottleneck for automatically moving files. As previously mentioned by the other users, they said I can execute the package from SSMS and point to any instance of a client machine. I have not been able to find and documentation or examples of this. – AlanPear Feb 11 '18 at 18:55
  • If you create a shared folder on your local machine, and share it with the network user account used by your SQL Server's Agent, then the SSIS package on your SQL Server will be able to access the files on your local machine, using the path to the share. – Tab Alleman Feb 14 '18 at 19:25
  • Perfect! Thank you for the response – AlanPear Feb 14 '18 at 19:56

0 Answers0