We're developing the next iteration of some software which basically collects data, stores it in SQL as a combination of columns or as JSON within columns, and stores physical files associated with each record.
During the first iteration we ran into a number of issues related to file/folder names.
Windows presented some challenges in terms of characters that are allowed in file names IIS presented some issues related to certain characters such as the + symbol, but I think this boils down to URL encoding So we've come up with some strategies to resolve all these issues, but the challenge I'm left with is how to transform all the old data and physical files into the new formats.
Potential Solutions
We could write our own code to do this. I have experience with this and know that it can work, but can also add a tremendous amount of time to development, not to mention having to maintain another codebase if it were to be needed in future.
Alternatively, we could purchase a tool that can do this, but it would have to cater for both critical needs:
- Transform mainly the JSON data within the tables, which in turn point to physical files and
- Shuffle the files around into different folders and rename them according to our new strategy.
We could also try SSIS although I've never worked with it and not sure if it can accommodate the second requirement of working with thousands of files on disk.
Are there any tools in the market that can do this?
Environment
SQL Server 2019
.Net Framework 4.7
