CODEWITHSUNDEEP
sqlparametersssisodbc
Jooooosh
Jooooosh

Reputation: 331

SSIS ODBC SQL parameters

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?

Upvotes: 10

Views: 24181

Answers (1)

april4181
april4181

Reputation: 596

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.

enter image description here

Upvotes: 23

Related Questions