Many times you have a scenario where you have a process that is watching some directory for certain file names. Once a file is created by SSIS components (see below) the other process then reads it or moves it to some other folder for further processing. The issue is data flow can take longer to write many rows to a file, during that file is locked but same time other process starts reading the partial file. To avoid this issue you can use the workaround listed in this topic.
For creating JSON / XML or CSV File you can use the following destination components.
**Rename the File after it’s written completely **
Let’s say the Vendor process is looking for the Orders.csv file once is fully written, for that, we can use the *.tmp extension while the file write is in progress, once the write is done we can rename it to Orders. csv.
- Create data flow with any destination mentioned previously
- In the File destination use File Path as c:\data\Orders.tmp in the connection (For JSON / XML) or in Properties if its CSV File destination
- After data flow attach SSIS Advanced File Operation Task
- Change operation to Rename File in Advanced File Task and configure for the same.
Source=c:\data\orders.tmp
Target=orders.csv
