0

I have an SSIS package for deployment into a SQL Server 2012 SSISDB and it uses a Lookup transformation. I am using a result from a SQL query to perform the lookup comparison.

enter image description here

This does not work and I get all rows as "No matched".

The query is the following:

DECLARE @LastJobDate DATETIME

SELECT @LastJobDate = COALESCE(MIN(S.LastImportDate), DATEADD(DAY, -2, GETDATE()))
FROM Stations S INNER JOIN    
     Lines L ON S.ID_Line = L.ID_Line
WHERE L.Name LIKE 'lineType%' AND S.ImportData = 1 AND S.Active = 1

SELECT J.ID_Line, J.ID_Job, J.SerialNumber
FROM [Jobs] J INNER JOIN            
     [Lines] L ON J.ID_Line = L.ID_Line
WHERE L.Name LIKE 'lineType%'AND J.TimeStamp >= DATEADD(HOUR, -1, @LastJobDate)

By accident, I found that if I place a [SET NOCOUNT ON] at the beggining of the query, it will work.

DECLARE @LastJobDate DATETIME
SET NOCOUNT ON

SELECT @LastJobDate = COALESCE(MIN(S.LastImportDate), DATEADD(DAY, -2, GETDATE()))
FROM Stations S INNER JOIN    
     Lines L ON S.ID_Line = L.ID_Line
WHERE L.Name LIKE 'lineType%' AND S.ImportData = 1 AND S.Active = 1

SELECT J.ID_Line, J.ID_Job, J.SerialNumber
FROM [Jobs] J INNER JOIN            
     [Lines] L ON J.ID_Line = L.ID_Line
WHERE L.Name LIKE 'lineType%'AND J.TimeStamp >= DATEADD(HOUR, -1, @LastJobDate)

Am I missing something? Why this behavior?

nunopacheco
  • 71
  • 3
  • 11
  • Here is what `SET NOCOUNT ON` does: http://stackoverflow.com/questions/1483732/set-nocount-on-usage It might be that teh queries before, were sending the record count and therefore corrupting your Lookup. – Nebi Sep 09 '16 at 09:11
  • @Nebi, Indeed it seems like that, I will read more carefully about what `SET NOCOUN ON` really does, instead of searching issues on LookUp Transformation. – nunopacheco Sep 09 '16 at 09:20
  • I would also recommend looking up what 'blocking' transformations are. If your `aggregate` transformation will not progress until all rows are brought through which can have a negative impact on performance. Almost any kind of aggregation for the purposes of reconciliation or logging can be more efficiently achieved with `Execute SQL` tasks in your Control Flow. – iamdave Sep 09 '16 at 12:43
  • @iamdave, I have to admit that I didnt know that. I will test it and check the impact. I will also read about the blocking transformations. Thanks! – nunopacheco Sep 09 '16 at 13:57

1 Answers1

2

Why this behavior?

An SSIS Lookup Component can only consider the first result returned by a multi-statement query such as yours.

When you don't have SET NOCOUNT ON, the first result returned by your query will be the message "1 row(s) affected" or something like that. The Lookup Component will not be able to look at the result set returned by the second half of your query.

This is why setting NOCOUNT ON fixes the problem. The "row(s) affected" message will not be returned by the first part of the query, and the only thing returned will be the resultset of the second part of the query, which the Lookup Component will then process.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • after some reading about the `SET NOCOUNT ON` option, I see know the impact that it can have on SPs, transformations, etc.. Thanks! – nunopacheco Sep 09 '16 at 14:01