I have been doing more SSIS work this year than in the past 7 years combined. And, wow, I am learning so much! Since T-SQL is my favorite part of SQL Server, at first I approached the work with that mindset, expecting things to work the same. One transform in particular, the Lookup transform, has given me more than its share of problems.
Just today I was comparing the data from a staging database to a target database to delete rows in the target when they no longer appear in stage. I painstakingly wrote the two queries I needed and compared the results in SSMS. Everything looked great, and the data flow at this point should not have deleted anything. When I ran the data flow, much to my surprise every single row from the table was deleted! After checking and rechecking everything, I could not see anything wrong with the settings. Finally I remembered I had seen this problem before a couple of months ago.
Like many challenges with SSIS, the problem involved data types. One of the columns in the source was a Numeric (9,3) while the column to match it in the lookup transform was the result of an expression. Even though the data looked identical, the rows didn’t match according to the transform and every row went down the no-match path.
To demonstrate this and how to fix it, create a new package and add a data flow. On the data flow add an OLE DB Data source pointing to your version of AdventureWorks. Change the “Data access mode” to “SQL command” and type in this query:
SELECT DISTINCT RejectedQty FROM Purchasing.PurchaseOrderDetail; --Numeric(8,2)
Add a Lookup transform and connect the data source to it. Change the “Specify how to handle rows with no matching entries” to “Redirect rows to no match output” on the General page.
On the Connection page, connect to the AdventureWorks database. Select “Use results of a SQL Query” and type in this query:
SELECT 1 AS QTY;
On the Columns page, you map a column or columns from the source to the data in the lookup. This is similar to joining two tables. Click the Columns page and try to connect the RejectedQty from the source to the QTY from the Lookup transform. When you do, you will receive an error about incompatible data types.
Using T-SQL you could join these two tables successfully. Even though the data types are not the same, they are compatible. You might take a performance hit due to converting one data type to another, but you could run the query and get results.
Click OK to dismiss the error. So, what data type is the QTY in the lookup? To find out, check the QTY column to include it in the output and click OK. The transform will still not be happy, but you can now right-click on the transform and select “Show Advanced Editor”. Once the “Advanced Editor for Lookup” dialog opens, select the “Input and Output Properties” page. Expand “Lookup Match Output” and “Output Columns”. Select QTY. Take a look at the Data type and you’ll see that it is a four-byte integer.
Unfortunately, the lookup transform does not let you change the data type here. Modify the query in the lookup transform to the following:
SELECT DISTINCT StockedQty AS QTY FROM Purchasing.PurchaseOrderDetail
If you take a look at the data type again, you will see that it is now a Numeric (9,2). You can now connect the RejectedQty to the QTY column since the data types are the same, just not the same size.
Drag a Derived Column transform to the data flow and connect it from the lookup transform. You will be prompted to select the type of output. Choose “Lookup Match Output” and click OK. Drag in a second Derived Column transform and connect it up as well.
Run the package. Because the data types are not identical, all of the rows go down the no match path.
To fix this issue, the data types must be identical. You can often modify the data type in the advanced editor of sources or transforms like the derived column transform. Open up the Advanced Properties editor of the OLE DB Source. Change the RejectedQty data type to a numeric (9,2) from numeric (8,2).
Click OK and then run the package. This time, some of the rows go down the matching path as expected.
Data type discrepancies are common issues when debugging SSIS packages. Be sure to take a look at the Advanced Editor on data sources and transforms when you run into these problems.