Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 577
Jooooosh Avatar asked Nov 05 '14 16:11

Jooooosh


People also ask

How do I use parameters in SSIS?

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.

How do you pass parameters to data flow in SSIS?

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.

What is ODBC parameter?

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.


1 Answers

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

like image 176
april4181 Avatar answered Oct 19 '22 08:10

april4181