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?

Question

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.

Answer 1

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 

Answer 2

STRING_SPLIT alternative

As mentioned in the following answer T-SQL split string, the following SQL query can replace the STRING_SPLIT function:

SELECT LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'Value' 
FROM  
(     
SELECT CAST ('<M>' + REPLACE(/*Comma separated value should be placed here*/, ',', '</M><M>') + '</M>' AS XML) AS Data            
) AS A 
CROSS APPLY Data.nodes ('/M') AS Split(a)

Using OLE DB Source SQL Command

If you are using an OLE DB Source component, you can use the following SQL Command:

DECLARE @String varchar(100) = ?;

SELECT * FROM PRODUCTS
WHERE PRODUCT_NAME IN (
SELECT LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'Value' 
FROM  
(     
SELECT CAST ('<M>' + REPLACE(@String , ',', '</M><M>') + '</M>' AS XML) AS Data            
) AS A 
CROSS APPLY Data.nodes ('/M') AS Split(a)
)

Test

Using the AdventureWorks2017 database, I used the following query to search for the person's information stored in the [Person].[Person] table while the filter is on the PersonType column:

DECLARE @String varchar(100) = ?;

SELECT * FROM [AdventureWorks2017].[Person].[Person]
WHERE PersonType IN (
SELECT LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'Value' 
FROM  
(     
SELECT CAST ('<M>' + REPLACE(@String , ',', '</M><M>') + '</M>' AS XML) AS Data            
) AS A 
CROSS APPLY Data.nodes ('/M') AS Split(a)
)

In the OLE DB Source Editor, if we click on the Parameters button, a parameter is recognized. I will create a new variable and use it as a parameter as shown in the image below:

The variable data type should be set to String and the value is set to EM,SC which are both symbols used in the PersonType column.

Now, if we click on the Preview button in the OLE DB Source Editor, the accurate data is visualized.

Answer 3

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.