Automate process of streamlining data production

Recently I have been working on defining and implementing the automate process for our data production streamlining.

The process overview looks like:

After a few hours of barnstorming and discussion, I can clearly define 4 important steps for the whole process, in which we specially need to handle from step 2 onward.

Step 1: stakeholder needs to provide the source data via FTP upload which we need to provide the setup

1. What is the frequency of upload ?
2. What is the naming convention for upload file?
3. How to setup up secure FTP for oversea user?

Step 2: Monitor FTP Folder to check if file is complete

Objective: To make sure the source is fully uploaded and in correct defined format

1. What is the naming convention for upload file?
2. How to check if file name is correct ?
3. What is the required input before any checking process?
4. What is the workflow?

Step 3: Pre-processing.

Objective: To pre-process the uploaded source to corresponding source database.

1. What are the scenarios to handle ?
2. How to run restore database  via script?
3. How to copy data from .mdb or accdb to MS SQL database via script?
4. How to handle cases of failure ?
5. What is the reports to validate ?

Step 4: Setup the task scheduler to perform data conversion from source database to expected format database.

Objective: To define the frequency for data release and setup scheduler task to perform the data conversion

1. What task scheduler to use?
2. How to setup the scheduler?
3. How to handle cases of failure?
4. What is the report to validate?

Basically, I have done the initial draft for the process document. Things to do next is the details strategy for each steps and the actual feasibility study on  implementation. Hope we could get all things done on time which is expected by early next week.

Time to work ! 🙂