Using SSIS Parameters in LIKE Query

Question

I'm not able to find the right syntax to use a parameter within a LIKE clause in an SSIS SQL Server OLEDB Source component. For a trivial example, consider the following single-column table "tblTeams", listing teams and team members as a single string (it's stupid, I know):

╔═══════════════╗
║ TeamMembers   ║
╠═══════════════╣
║ A - Joe       ║
║ A - Melissa   ║
║ B - Frank     ║
║ C - Angela    ║
║ A - Luis      ║
║ C - Ashley    ║
║ B - Sam       ║
╚═══════════════╝

I want to run a for loop that exports files of members per team. The loop has an input variable from an array of team names: "A", "B", "C", etc. I'd like to use that variable as a parameter in an OLE DB Source to select the team members as such:

SELECT  [TeamMembers]
FROM    tblTeams
WHERE   [TeamMembers] LIKE '?%';  --where ? is param = "A", "B", or "C"

However, the syntax here considers the parameter placeholder to be question mark string. Moving the placeholder outside breaks the LIKE clause. Is it possible to use an SSIS parameter in a LIKE?

you will have to use findstring function http://stackoverflow.com/questions/4739230/is-it-possible-to-perform-a-like-statement-in-a-ssis-expression — niketshah90, Jul 26 '16 at 02:06
try SELECT [TeamMembers] FROM tblTeams WHERE [TeamMembers]like ? + '%' — sandeep rawat, Jul 26 '16 at 06:51
@ChintuS FINDSTRING is not directly applicable here because I am writing a SQL Query, not an expression. A good suggestion though, thanks. — BradV, Jul 26 '16 at 14:30
@sandeeprawat I did not expect your solution to work because of the single quote placement, but it does! If you repost as an answer, I will accept it. — BradV, Jul 26 '16 at 14:39

score 2 · Answer 1 · answered Jul 26 '16 at 08:36

Create variable SQL_Select type String, and define an Expression "SELECT [TeamMembers] FROM tblTeams WHERE [TeamMembers] LIKE '" + [User::TeamMemberName] + "%'" on this variable. Here I assumed you used a variable TeamMemberName type String with aforementioned param A, B or C.
Then on OLE DB Source component - set SQL command from variable ad Data access mode and select User::SQL_Select in a variable name drop-down.

score 1 · Accepted Answer · answered Jul 28 '16 at 05:52

1

We can use ? and map it to string data type .

IE SELECT [TeamMembers] FROM tblTeams WHERE [TeamMembers]like ? + '%'

As ? is automatically converted in proper data format.

answered Jul 28 '16 at 05:52

sandeep rawat

4,797
1
18
36

Using SSIS Parameters in LIKE Query

2 Answers2