title: How to using STRING_SPLIT within an SSIS OLE DB Source SQL Command when the database compatibility level is set to is set to SQL Server 2008?
body: <p>I have an SSIS package on server 1. It does a SQL query on SQL db located on server 2 via the OLEDB source sql command text. The query is:</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT * FROM PRODUCTS
  WHERE PRODUCT_NAME IN (?)
  </code></pre>
  <p>This fails, since? is a scalar value and not a table. To fix this there are 2 options:</p>
  <ol>
  <li>Use STRING_SPLIT</li>
  <li><a href="https://www.aspsnippets.com/Articles/Use-Comma-Separated-Delimited-string-values-with-IN-and-WHERE-clause-in-SQL-Server.aspx" rel="nofollow noreferrer">Create string split function</a></li>
  </ol>
  <p>I can't use option 1 because although it is SQL server 2017, the DB compatibility level is set to 2008 (Level 100). STRING_SPLIT is supported only for a higher compatibility level. I'm not allowed to change this.</p>
  <p>I can't use option 2 because I am not allowed to create any new custom functions on that database.</p>
  <p>Is there a workaround? I have read about adding the custom function into the master DB, but unsure whether future SQL updates may reset it as user functions are not meant to be placed inside the master DB.</p>