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:

SELECT * FROM PRODUCTS
WHERE PRODUCT_NAME IN (?)

This fails, since? is a scalar value and not a table. To fix this there are 2 options:

1. Use STRING_SPLIT
2. Create string split function

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.

I can't use option 2 because I am not allowed to create any new custom functions on that database.

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.

CodePudding user response：

One way would be switch context to a database that does have the required compatibility level (tempdb below).

DECLARE @ProductNames VARCHAR(MAX) = ? 

CREATE TABLE #ProductNames
(
PRODUCT_NAME VARCHAR(50) PRIMARY KEY
)

EXEC tempdb.sys.sp_executesql N'INSERT INTO #ProductNames SELECT DISTINCT value FROM STRING_SPLIT(@ProductNames, '','')',
                       N'@ProductNames VARCHAR(MAX)',
                       @ProductNames = @ProductNames;

SELECT *
FROM   PRODUCTS
WHERE  PRODUCT_NAME IN (SELECT pn.PRODUCT_NAME
                        FROM   #ProductNames pn)

DROP TABLE #ProductNames 

CodePudding user response：

You can load you string into an parameter object @ProductNames and then use that as a source and then inner join the product table in another data flow.

1st DF: Script Component Source with @stringToParse as a variable. Also have output string in DF of ProductName.

Code:

string prods = Variables.stringToParse.Value;
string[] productNames = prods.Split(',');
foreach(string p in productNames)
{
    OutputBuffer0.AddRow();
    OutputBuffer0.ProductName = p;
}

Then load the results into a recordset destination mapped to @ProductNames.

2nd DF: 2 sources.

1. Product Table
2. Your recordset @ProductNames

do a merge join (inner) and you will have your records.