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?
Package parameters allow you to modify package execution without having to edit and redeploy the package. In SQL Server Data Tools you create, modify, or delete project parameters by using the Project. params window. You create, modify, and delete package parameters by using the Parameters tab in the SSIS Designer.
Select the SQL command option for data access mode, and then type a parameterized query in the SQL command text pane. Click Parameters. In the Set Query Parameters dialog box, map each parameter in the Parameters list to a variable in the Variables list, or create a new variable by clicking <New variable>. Click OK.
You specify connection parameters when you create a data source or modify a data source connection. For more information, see Data Source Creation and Add or Modify a Data Source Connection.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With