Database Reference
In-Depth Information
Now that you have walked through the creation of the connection manager and pro-
vider and decided which source component to use, you need to set up the metadata for
pulling the data. You do this by selecting what type of access you want to make and
then adding the query information to the source component. In addition, you will want
to review a few patterns when you're setting up the query and column metadata. Let's
get started.
ADO.NET Data Access
If you decided to use the ADO.NET source component, either with the ADO.NET or
ODBC provider, you have two options to select what data you want to see:
Table or View:
Select which table or view from which you want to
receive data. The list of tables and views should be prepopulated and
listed based on your access. We do not recommend this option be-
cause it includes unnecessary columns, even if you restrict the
column list in the component.
SQL Command:
Enter text that will be executed on the SQL Server
database.
Because the Table or View option is not our recommended option, let's dig into the
SQL Command option a little deeper. You can enter either a direct SQL query that re-
turns a dataset or a stored procedure using the
EXEC
statement.
Whether you are using a SQL query or executing a stored procedure, you will need
to be aware that the ADO.NET source does not allow you to use parameters in your
query. If you need to modify the query that gets used, you will need to use an expres-
sion. Expressions are only set at the control flow level, so you will need to take a look
there to set up your expression. Follow these steps to set a new SQL command at
design time:
1. When in the Data Flow task, click the background to ensure no com-
ponents are selected and look in the Properties menu for the
Ex-
pressions
property.
2. Once the Expression Property window is open, select the [ADO NET
Source].[SqlCommand] option in the Property field and click the el-
lipses button next to the Expression field.
Search WWH ::
Custom Search