SSIS ODBC SQL parameters

Question

I've got a SQL command in an odbc source data flow task that needs to take parameters, but the option to add them isn't there.
I tried to add the database as an ADO.NET connection with an ODBC provider, but there were also no parameters available. Also tried it as an OLEDB connection, but there's no provider available for ODBC. The variables needed are set, I just can't add them as parameters.

So the main thing I'm wondering are:
Is if there's a way to add parameters to a SQL command in an ODBC source
Is there an OLEDB provider for ODBC I can use?
Can I access the package variables directly in the query? Will that leave me open for a sql injection? Like this.

"SELECT * FROM MyTable WHERE [id] = " + @[User::id]

Edit:
Here's the altered expression

"SELECT Name, PhoneNum, Address FROM PERSON<br>
WHERE Name = '" + @[User::Name] + "'<br>
AND PhoneNum = '" + @[User::PhoneNum] + "'"

It generates this in the source:

SELECT Name, PhoneNum, Address FROM PERSON<br>
WHERE Name = ''<br>
AND PhoneNum = ''

Will it fill in the quotes based on the value?

Did you try looking at using expressions in the connection string? — Tab Alleman, Nov 05 '14 at 16:33
The connection string works. Do you mean the sql command from variable option? That doesn't show up in the list of options. — Jooooosh, Nov 05 '14 at 16:37
Sorry I thought you meant parameters in the connection, not the query. — Tab Alleman, Nov 05 '14 at 16:44
I'm gonna try doing this through a script and see how that goes. — Jooooosh, Nov 05 '14 at 18:58
Good luck, I see that ODBC doesn't show up in SSIS until 2012, and I'm still on 2008R2. — Tab Alleman, Nov 05 '14 at 19:02

score 23 · Accepted Answer · edited Feb 04 '19 at 19:07

23

You have to use the expression builder outside the data flow. Go to the control flow tab, select the Data Flow that contains the ODBC or ADO Net Source, and look at the properties window. You'll see the properties for the ODBC or ADO Net Source there, as well as an "Expressions" property, where you can set the expression to generate your dynamic SQL.

EDIT

Here's an image of where you'd change the property. You need to set a generic SQL statement in your data flow, click okay, and go back into the control flow. Then right right click on your data flow and click properties. Under expressions, you can select your SQL command and build it with variables.

edited Feb 04 '19 at 19:07

Hadi

36,233
13
65
124

answered Nov 05 '14 at 19:03

april4181

596
3
8

That would dynamically fill in a variable with the SQL command right? I don't have that option in the ODBC source. Only select from table and sql command. – Jooooosh Nov 05 '14 at 19:41
@Jooooosh - You're right, it doesn't give you that option. This is a work-around. You'll need to use the property expression editor to create a dynamic sql statement. I've elaborated my answer. – april4181 Nov 05 '14 at 20:01
OK I see what you mean now. The SQL Command property changes the command in the source. Does this look like the correct syntax for the expression? ="SELECT Name, PhoneNum, Address FROM PERSON WHERE Name = " + @[User::Name] + " AND PhoneNum = " + @[User::PhoneNum] + "\"" – Jooooosh Nov 05 '14 at 20:40
I put one that runs in my answer, but it doesn't get any data. Could you take a look at it? – Jooooosh Nov 05 '14 at 21:21
If it runs, but doesn't return results then it sounds like one of the values for your variables might not be correct. Have you tried removing them and running it? I can take a look at it if that doesn't work. – april4181 Nov 05 '14 at 22:31
Yeah it's the data I'm searching for. Just gotta figure that part out. Thanks for your help. I'll probably be using this a lot. – Jooooosh Nov 06 '14 at 14:18
I struggled with not seeing the SQL Command property in VS 2017 15.9.14 and SSDT 15.9.2 for about an hour. Eventually it just appeared... Restarted multiple times in between and updated everything. I have no idea what fixed it... – Luke Jul 29 '19 at 14:44

SSIS ODBC SQL parameters

1 Answers1

Linked