SSIS Data Flow, ignoring where clause in SQL Statement

Question

Using Visual Studio 2017 and SSIS.

Basic data flow from Oracle to SQL Server.

Oracle is source (OLEDB) and SQL Server is destination (OLEDB also).

I'm using a SQL Command of

SELECT * FROM CASE_NOTE WHERE
CREATE_DT <= '31-DEC-2010 00.00.00'

However, the data flow is ignoring the WHERE and migrating all rows.

Also, the dataflow will show 1.5 million rows with 750,000 committed (the source only have 750,000 rows).

This is strange behaviour and I cannot work out what is wrong.

I've tried going from OLEDB to ADO.NET and have the same result.

I've removed the where clause, but the 2x rows through the dataflow persists

Answer 1

Problems with parsing string to date ?

Can you try this way:

SELECT * FROM CASE_NOTE WHERE CREATE_DT <= '2010-DEC-31'

---

Another striking thing that you use OLEDB Oracle source provider.

Perhaps it is obvious default option when you have clean install of SSIS , however, Microsoft offers another more performant and preferable choice:

Microsoft Connectors for Oracle and Teradata by Attunity for Integration Services (SSIS)

Perhaps this alternative will results into your better experience with SSIS

Answer 2

This is an old thread, but I just spent an hour with the same problem and think I know what went wrong.

If you are developing multiple versions of the same or similar dataflows in one package, remember that ALL dataflows in the package will be run if you hit the start arrow at the top of the SSIS window. I wonder if that is what happened to the person who posted the original entry. That explains the double number of entries in the table and the appearance that the WHERE clause was not working.

If you are in the window with the dropdown that selects one data flow for editing, it appears that the arrow would run only the data flow that is visible for editing, but it even runs the hidden ones, too.

To run a single data flow, right click in empty space and select Execute Task.