Overview
The Database Source Component is an SSIS Data Flow Component for loading data from a database table, a view or SQL command. The component can be configured for ADO.NET, OLE DB and ODBC connection managers (see a list of supported databases below). Error redirection is available.
Tested databases and data providers/drivers
| Database | ADO.NET | OLE DB | ODBC |
|---|---|---|---|
| SQL Server | SqlClient Data Provider | SQL Server Native Client 11.0 | SqlServer Native Client 11.0 |
| Oracle | ODP.NET (managed driver) | Oracle Provider for OLE DB | Oracle ODBC driver with Oracle Instant Client |
| PostgreSQL | Npgsql Data Provider | MSDataShape | PostgreSQL ANSI |
| DB2 | IBM DB2 .NET Data Provider | Microsoft OLE DB Provider for DB2 | IBM DB2 ODBC Driver |
| MySQL | MySql Data Provider | MSDataShape | MySql ODBC 8.0 Unicode Driver |
| Teradata | .NET Data Provider for Teradata | MSDataShape | Teradata Database ODBC Driver |
Editor
In the Database Source editor the general parameters are configured. The data is extracted from a table, a view or an SQL query.
Use the Columns tab to map an output column to each external (source) column.
You can preview the first 100 rows of the data retrieved in the Preview tab.
Use the Error Output tab to select error handling options and to set properties on error output columns.
Quick Start
In this section we will show you how to set up a Database Source component.
- First we need to create the Database Source component. Open Visual Studio.
- Drag the Data Flow Task from the SSIS Toolbox to the Control Flow canvas.
- Click the Data Flow tab to go to the Data Flow canvas.
- In the SSIS Toolbox, locate the Database Source component and drag it onto the Data Flow canvas.
- Double-click on the component on the canvas.
- In the Database Source component window click on New... connection button.
- Select connection type between ADO.NET, OLE DB and ODBC.
- Create the connection.
- Once the connection is created the list with Table or view will get populated.
- Select Table or view from the list or specify an SQL Statement in editor below.
- You can preview the data in Preview tab and configure Error Output if needed.
- Click OK to save configuration and to close the component editor.
Congratulations! You have successfully configured the Database Source component.
Parameters
Configuration
Use the parameters below to configure the component.
Select an existing ADO.NET, OLE DB or ODBC connection manager.
Related Topics: ADO.NET Connection Manager, OLE DB Connection Manager, ODBC Connection Manager
Select database table or view.
Specify an SQL statement for data extraction.
Knowledge Base
What's New
- Fixed: Replacement of variable with actual value in the statement (Thank you, Sophia).
- Fixed: Design time issue when using SELECT DISTINCT clause.
- Fixed: Issue with handling nvarchar(max) columns in SQL Server.
- New: Introduced component.
COZYROC SSIS+ Components Suite is free for testing in your development environment.
A licensed version can be deployed on-premises, on Azure-SSIS IR and on COZYROC Cloud.