Check out the solution in the video Schema validation failed: 'ISJSON' is not a recognized built-in function name. First, note the version of Microsoft SQL Server that we're using. This is key because we'll see this error if we're using an older version of Microsoft SQL Server. Second, before upgrading our SQL Server environment, we want to evaluate what we have and be sure that this is the best choice (see below for more information for considerations). Finally, we have some options outside of SQL Server to convert JSON if we need to get data from a JSON format before extracting the data and importing it into SQL Server.
Check out the highest-rated Automating ETL course on Udemy, if you're interested in data.
We will find that some environments are using legacy systems (this may have advantages in both cost and security). While we can upgrade these environments, we will want to ensure that our database environment will be compatible with an upgrade. Changes may affect existing code, especially if code or features are deprecated. Also, we want to ensure that our environment can handle this security and cost change - these two cannot be overlooked. Upgrading an environment for one function is definitely not worth it, but if there are other reasons to upgrade, this may be worth the effort. This requires a comprehensive review of the environment. In the meantime if we cannot immediately upgrade our environment, we can look at alternatives converting JSON, such as scripting languages or the language we're using in our software application (if applicable). In general, depending on the JSON file or source, this may be faster. For instance, extracting and designing a schema from a JSON API source would be much faster with a scripting or OOP language in general.