SQLS ODBC SQL Options
I have a SQL command on an odbc raw data flow task that needs to accept parameters, but there is no way to add them.
I tried to add the database as an ADO.NET connection with an ODBC provider, but there were no options available either. Also tried this as an OLEDB connection, but there is no provider for ODBC there. The required variables are set, I just can't add them as parameters.
So, the main thing I'm wondering is:
Is there a way to add parameters to the SQL command in the ODBC source Is there an OLEDB provider for ODBC that I can use?
Can I access package variables directly in the request? Will this leave me open to SQL injection? Like this.
"SELECT * FROM MyTable WHERE [id] = " + @[User::id]
Edit:
Here's the modified expression
"SELECT Name, PhoneNum, Address FROM PERSON<br>
WHERE Name = '" + @[User::Name] + "'<br>
AND PhoneNum = '" + @[User::PhoneNum] + "'"
It generates this in source:
SELECT Name, PhoneNum, Address FROM PERSON<br>
WHERE Name = ''<br>
AND PhoneNum = ''
Will it fill in the quotes based on the value?
source to share
You must use the expression builder outside of 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. There you will see properties for ODBC or ADO Network Source, as well as the Expressions property where you can set an expression to generate dynamic SQL.
EDIT
Here is an image where you would change the property. You need to set up a generic SQL statement in the data flow, click OK and return to the control flow. Then right-click on the data stream and select Properties. In expressions, you can select an SQL command and build it with variables.
source to share