CODEWITHSUNDEEP
sqlsql-servert-sqlssislookup
Jonathan Porter
Jonathan Porter

Reputation: 1556

SSIS Lookup transformation not capturing non matching values

I'm using the lookup transformation to look back at my data warehouse table to find any deleted records from production and then send them to a staging table to be removed from the datawarehouse table.

Logically, if a record is removed in the production table and exists in the datawarehouse table this lookup should find it and redirect it to No Match Output correct?

The issue is the lookup isn't finding the non-matching records or I'm doing something backwards.

I should add that in production there are 4 identical databases for four different regions. My package loops through each one by looping through the connection strings in a config file. Then all of these records are marked with which database they came from and added to the datawarehouse table.

This is the part inside my data flow task that isn't working properly:

enter image description here

Inside the "Fact Table" OLE DB source I'm pulling the records from my fact table where the Region equals whichever region the connection string identifies by using numbers 1 - 4. I put the query in a variable so that it can be dynamic. I'm also only pulling the columns that cannot be null inside the staging table in order to minimize the amount I'm pulling and I only really need the ID and Region column to delete the records because they should be unique within their respective regions.

Inside FindDeletes variable:

"SELECT ID ,
        ParentItemID ,
        IsTransPerPiece ,
        PerPieceCostQuantity ,
        PerPieceCost ,
        PerPieceUnitCost ,
        OriginalPerPieceCostQuantity ,
        OriginalTransFee ,
        TransFee ,
        CostTrans ,
        CostLabor ,
        OriginalCostQuantity ,
        OriginalFacilityFees ,
        FacilityFees ,
        Region ,
        HashValue ,
        CreateDate 
FROM dbo.FactInvoiceWasteManifests
WHERE Region = CASE " + (DT_WSTR, 2) @[User::ConnectionStringID] +
       " WHEN 1 THEN 'NE'
        WHEN 2 THEN 'BALT'
        WHEN 3 THEN 'NY' 
        WHEN 4 THEN 'PA'
        END "

Inside OLE DB Source showing the variable query:

enter image description here

Next we go into the transformation in which I redirect rows that don't match to the No Match Output

enter image description here

The connection here is to production and the query here is again dynamic because the Region column doesn't exist in production.

enter image description here

Showing the expression for the lookup SQL query:

enter image description here

"SELECT  ID,
        CASE " + (DT_WSTR, 2) @[User::ConnectionStringID] +
       " WHEN 1 THEN CAST('NE' AS NVARCHAR(25))
        WHEN 2 THEN CAST('BALT' AS NVARCHAR(25))
        WHEN 3 THEN CAST('NY' AS NVARCHAR(25))
        WHEN 4 THEN CAST('PA' AS NVARCHAR(25))
        END AS Region 
FROM    dbo.InvoiceWasteManifests;"

UPDATED QUERY:

"SELECT  ID,
        CASE 
        WHEN " + (DT_WSTR, 2) @[User::ConnectionStringID] + "=1 THEN CAST('NE' AS NVARCHAR(25))
        WHEN " + (DT_WSTR, 2) @[User::ConnectionStringID] + "=2 THEN CAST('BALT' AS NVARCHAR(25))
        WHEN " + (DT_WSTR, 2) @[User::ConnectionStringID] + "=3 THEN CAST('NY' AS NVARCHAR(25))
        WHEN " + (DT_WSTR, 2) @[User::ConnectionStringID] + "=4 THEN CAST('PA' AS NVARCHAR(25))
        END AS Region 
FROM    dbo.InvoiceWasteManifests;"

enter image description here

Lastly, I join the two tables on Region and then search on ID. The result is nothing happens and no records are redirected anywhere.

Upvotes: 2

Views: 2389

Answers (2)

Jonathan Porter
Jonathan Porter

Reputation: 1556

It turns out I didn't fully understand how the Lookup Transformation works. I solved my problem this morning by also using the ID column as a join instead of caching it.

enter image description here

Upvotes: 2

LONG
LONG

Reputation: 4610

This is the first dynamic query you need to put:

  --declare @sql varchar(max)
  --declare @ConnectionStringID varchar(50) =1
    set @sql = 
    'SELECT ID ,
            ParentItemID ,
            IsTransPerPiece ,
            PerPieceCostQuantity ,
            PerPieceCost ,
            PerPieceUnitCost ,
            OriginalPerPieceCostQuantity ,
            OriginalTransFee ,
            TransFee ,
            CostTrans ,
            CostLabor ,
            OriginalCostQuantity ,
            OriginalFacilityFees ,
            FacilityFees ,
            Region ,
            HashValue ,
            CreateDate 
    FROM dbo.FactInvoiceWasteManifests
    WHERE Region = CASE 
            WHEN ' +  @ConnectionStringID + '=1 THEN ''''''''+CAST(''NE'' AS NVARCHAR(25))+''''''''
            WHEN ' + @ConnectionStringID + '=2 THEN ''''''''+CAST(''BALT'' AS NVARCHAR(25))+''''''''
            WHEN ' +  @ConnectionStringID + '=3 THEN ''''''''+CAST(''NY'' AS NVARCHAR(25))+''''''''
            WHEN ' + @ConnectionStringID + '=4 THEN ''''''''+CAST(''PA'' AS NVARCHAR(25))+''''''''
            END' 
  --print (@Sql)

Upvotes: 0

Related Questions