SQL-SSIS. Find missing numbers in table

Question

I'm using ssis 2008 and sql server 2012. I have a table with column FileNumber. I would like to make sure all the numbers are following and there are no wholes. No missing numbers. For example:

FileNumber
--------------
110
111
112
114
115
117

I would like the ssis to output the numbers 113 and 116 which are missing.

Any idea how to do this?

Answer 1

Try this:

DECLARE @t table(
 FileNumber int
 );

INSERT INTO @t(FileNumber) VALUES(110), (111), (112), (114), (115), (117);

SELECT t1.FileNumber + 1 AS sequance
FROM @t AS t1
LEFT OUTER JOIN @t AS t2 ON t1.FileNumber + 1 = t2.FileNumber
WHERE t2.FileNumber IS NULL