October 26, 2016 at 12:22 am
Our client described to us how they wanted to migrate their data from one datacentre to another datacentre as follows:
•An application will generate csv files to a folder inside a server (in Web Tier).
•They want the csv files to be imported to the database(node1) in real-time (meaning as soon as it is placed in the folder)
•The csv files will be deleted before another csv file is loaded to the folder and the cycle repeats.
•Configure transaction replication between Node 1 and Node 2 DB servers that are on the different sites. (only SQL port will be opened due to tight security rules)
My question is how to import csv files to database in real-time? Either a batch file program or SSIS will not be able to import real time because those tools need be scheduled (task manager / SQL job) and at certain intervals.
Is there a third-party tool that can be used to perform the importation of csv files to the database?
I told the project manager to approach the developers/application team to ask for suggestions.
The attachment shows the database migration diagram.
October 26, 2016 at 4:10 am
You probably could do this in SSIS using WMI tasks (WMI Event Watcher). You can set the task to wait for a file creation, and then once it has been move onto the next step.
If you have the task within a loop, you could have it effectively running continually, if this will be a ongoing project. I would, however, recommend testing in a dev environment first before implementing a "inifinite" loop, and ensure you still have some kind of "get out clause", along with decent error handling.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 26, 2016 at 6:06 am
Ricky Valencia (10/26/2016)
Our client described to us how they wanted to migrate their data from one datacentre to another datacentre as follows:•An application will generate csv files to a folder inside a server (in Web Tier).
•They want the csv files to be imported to the database(node1) in real-time (meaning as soon as it is placed in the folder)
•The csv files will be deleted before another csv file is loaded to the folder and the cycle repeats.
•Configure transaction replication between Node 1 and Node 2 DB servers that are on the different sites. (only SQL port will be opened due to tight security rules)
My question is how to import csv files to database in real-time? Either a batch file program or SSIS will not be able to import real time because those tools need be scheduled (task manager / SQL job) and at certain intervals.
Is there a third-party tool that can be used to perform the importation of csv files to the database?
I told the project manager to approach the developers/application team to ask for suggestions.
The attachment shows the database migration diagram.
Similar to the above suggestion, this can be achieved by using the FileWatcher task in SSIS.
Then create a SQL Agent job which runs the package once a minute. It the package is in 'waiting for a file' mode, the job will not run.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 2, 2016 at 8:39 pm
Hi Thom and Phil,
Thank you for your suggestions. We will check the WMI Event Watcher and File Watcher and test it out.
Best Regards,
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply