0

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:

  1. Transform mainly the JSON data within the tables, which in turn point to physical files and
  2. 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

2
  • Security rule 1: never let the users choose the file-name on your file-system (or don't use data defined by users or external systems as file-name). If you have to do so, apply a white-list that only allows ASCII characters or something similar safe and reject everything else.
    – Robert
    Jun 13, 2022 at 16:43
  • @Robert thanks Robert, yep some hard school fees I'm paying on this one.
    – Jacques
    Jun 14, 2022 at 14:42

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.