The OLE DB Source is used to fetch data from any database that supports OLE DB Connections. The SSIS Ole DB Source can connect to both Database Table and Views.
Most frequently the OLE DB Source in SSIS is used to fetch data from SQL Server Database. For that, the task has to be configured.
Let’s take it step by step:
– Find the Data Flow task in the SSIS Toolbox on the left and drag it in the control flow area;
– Double click on it to open the Data Flow;
– Find the OLE DB Source in the SSIS Toolbox (in the Other Sources group) and drag it in the Data Flow area;
– Double click on it to open the OLE DB Source Editor;
First Tab - Connection Manager
If you already have set up the OLE DB Connection Manager, simply choose it from the dropdown, otherwise, click on the “New” button. This will open up the Connection Manager, where the user can add, edit, and test the new connection.
Choose a Data access mode:
– Table or View – will allow the user to select a table or a view from the earlier specified OLE DB connection;
– Table name or view name variable – will allow the user to select a table or a view stored in variable;
– SQL command – will allow the user to write a custom SQL query.
Here you are provided with 4 options:
– Parameters – will allow the user to assign the appropriate parameters using the dialog box. This will only work if the query is parameterized;
– Build Query – will open a query designer thus allowing the user to design the query using a GUI;
– Browse – will allow the user to select a query file that is stored on the user’s machine;
– Parse Query – will check whether the query is successfully parsed or not.
– SQL command from variable – will allow the user to select an SQL query that is stored in a variable;
The Table or View will allow the user to select an entire table or view. The SQL Command will alow the user to fetch only the data he/she wants from a particular table, by filtering it using the “WHERE” statement, as an example.
Once a Data access mode has been selected and configured accordingly, click on the “Preview” button on the bottom, to preview the data that has been fetched.
Second Tab - Columns
Here, the user can verify which columns come with the table, and remove the unwanted columns if the case.
Third Tab - Error Output
This one is mostly used as a troubleshooting tool.
There are 3 possible functions:
– Redirect the truncated or the error rows to a different task;
– Ignore the truncated or error rows;
– Fail the task when a truncated or error row is found (this is the default option)
Once everything was configured, click on “OK”.