OLE DB Source Adapter
The OLE DB source connection has several Access Modes:
- Openrowset
- Openrowset From Variable
- SQL Command
- SQL Command From Variable
For now, I am going to focus on the SQL Command options. Using the SQL Command Access Mode requires you to set the SqlCommand property (typing in your SQL query directly), effectively ‘hard coding’ the sql statement into the package. Using the SQL Command From Variable option allows you to set the SqlCommand property using a variable and subsequently by using an expression.
Data Reader Source
The Data Reader Source connection has only one Access Mode, and that is SQL Command. Don’t ask me why. At this level you can only set the SqlCommand property by typing in your query, or indeed, copy & paste.
Using an Expression to set the SqlCommand Property
“So then,” you ask, “how might I set the SqlCommand property for each of these Data Sources when it seems that I can’t?”. Here’s how. Let’s first concentrate on the OLE DB Source:
- Create a new Variable of type string and set your SQL statement
- Open up the OLE DB Source and select the ‘SQL Command from Variable’ option
- Now, select the the variable created in step 1 as the variable that contains the SQL statement
At this point your OLE DB source is defined by the query that you have set in the Variable. We now need to create the expression that will define the query at runtime. We do this by navigating to the variable’s properties, setting the EvaluateAsExpression property to True and then configuring the expression in the Expression property.
Moving on to the Data Reader Source. With this adapter we only have the option of setting the SqlCommand directly. Or so I thought until recently. If you switch to the Control Flow design surface and select the Data Flow task that contains the Data Reader Source adapter, you will be able to set the SqlCommand using an expression.
- Select the Data Flow task to bring up its properties
- Click the ellipsis (…) to bring up the Expression Editor
- Select the [DataReader Source].[SqlCommand] Property
- Set your expression.
Using variables & expressions to set your queries enables you to centralise your queries, use configuration files to set database connections and capture the queries you are running in logging operations.