Single CSV file loaded to multiple servers.

Question

We have a 3 tier environment (Development, Testing and Production) solution. All 3 environments load data (SSIS) from the same source DBs into our 3 different BI environments. This works generally fine.

We also have user "dropped" CSV files to be loaded into each of the 3 BI environments. Our current approach is to copy the files to each 3 BI environment, so that the ETL jobs can load those files, but this creates additional dependencies and risk to copy the files and ensure each environment is kept in sync.

I would like to change this so that the ETLs from each BI environment loads the CSVs from the same single source File Location, then archives them after they've been loaded into each BI environment... so that the next user "drop" (usually weekly) doesn't overwrite the source files.

The steps might look like this:

1. User drops the files into a central File Location: "\FileDropbox\Source"
2. ETL on BI Production runs and loads CSV files from "\FileDropbox\Source" into a database table [BI_P].[Datamart].[dbo].[CSV_file]
3. ETL on BI Testing runs and loads CSV files from "\FileDropbox\Source" into a database table [BI_T].[Datamart].[dbo].[CSV_file]
4. ETL on BI Development runs and loads CSV files from "\FileDropbox\Source" into a database table [BI_D].[Datamart].[dbo].[CSV_file]
5. Original files are then archived: "\FileDropbox\Archive"

I'm trying to think of a solution that allows the above to be accomplished that also allows for the ETLs on the various environments to be executed in any order. ETLs on environments may run at various times/order, so can't be dependent on one another.

Currently thoughts are around ETLs that rename the source files by appending "P/T/D" to the filename after that environment has processed. ETL on Production would check for "P", Testing would check for "T" and Development would check for "D"... those ETLs would then process if the letter is missing. If all three letters exist (irrespective of which environment ETL ran) then the file would be archived.

Any thoughts or improvements or alternatives on the above?

Thanks in advance

Answer 1

Hi @Scott Kent-Collins ,

Why not directly use file system task to archive the csv file to the new folder after data flow task?

After the data flow task runs successfully, use the file system task to copy or move the file to archive folder.

If I misunderstand your need, please incorrect me.

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.