What SSIS transform can the "NOT IN" constraint used in the SQL query perform?
I have two OLEDB data sources that have similar columns:
TMP_CRUZTRANS
-------------
CUENTA_CTE numeric (20,0)
TMP_CTACTE_S_USD
----------------
CON_OPE numeric(20,0)
I need to subtract all the same values between these two tables and keep different rows. Is there a transform / task in SSIS that can fulfill a constraint commonly used in a SQL query? NOT IN
I am currently performing this operation using Execute SQL Task
on Control Flow
.
The top dataflow creates the first table TMP_CRUZTRANS
(Merge union between two other tables ... But I think it doesn't matter for my question) that I need to store different values in the second table.
In Execute SQL Task
I have the following statement:
INSERT INTO [dbo].[TMP_CYA]
SELECT RUT_CLIE, CUENTA_CTE, MONTO_TRANSAC
FROM [dbo].[TMP_CRUZTRANS]
WHERE CUENTA_CTE NOT IN (SELECT CON_OPE FROM TMP_CTACTE_S_USD)
Finally, with the new table, TMP_CYA
I can continue my work.
The problem with this approach is that it TMP_CRUZTRANS
got like 5 million rows, so it is very slow to insert all this data into the table using the Execute SQL Task. This operation takes about 5 hours to complete. So I need to do this in a data flow task.
source to share
You can use the data flow available in the task to achieve your requirements. Lookup transformation
Here's an example to show you what you are trying to achieve.
Create a data flow task package. Within a data flow task, use OLE DB Source to read data from the source table TMP_CRUZTRANS
. Use the Lookup transformation to test for the existence of values in a table dbo.TMP_CTACTE_S_USD
between the specified columns. Then redirect the mismatched output to the OLE DB Destination to insert rows into the tabledbo.TMP_CYA
This is what the dataflow task would look like instead of the SQL Execute task you are running.
Configure Lookup transformation
as shown below:
On the General tab, select Redirect rows to no match output
from Specify how to handle rows with no matching entries
because you are only interested in non-matching strings.
On the Connection tab, select the appropriate OLE DB connection manager and select the dbo.TMP_CTACTE_S_USD table. This is the table against which you want to check the data.
On the Columns tab, drag the CUENTA_CTE column and drop it into CON_OPE to establish a mapping between the source and lookup tables. Click OK.
When you connect a Lookup transformation with an OLE DB target, the Select Output dialog box appears . Be sure to select . Lookup No Match Output
Here's an example before executing the package.
You can see that only 2 lines not matching each other have been moved to the OLE DB destination.
You may notice that the target table now contains two non-matching rows after the batch is executed.
Hope it helps.