posted on Friday, January 20, 2006 2:30 PM
by
SimonSabin
SSIS: Use SQL Query in Lookups
How many of you have the same database in Dev, Test and Live but maybe slightly different column orders in your tables. This isn't an issue is it because we always use column names in SELECT, INSERT statements, don't we!!! No one uses SELECT * do they.
Well SSIS does, sort of, if you select the table option in the Lookup
What it does do is get the meta data for you table and stores it. Now if you've worked with SSIS or been to any SSIS talk you should know that SSIS at the runtime level deals buffers and the data in these is manipulated by the ordinal position of a field not its name. Well it seems that the Lookup does the same. It builds its internal structures based on the ordinal positions of the columns, at validation time it then validates that the column names (and data types I think) of columns it is using are the same as they were at runtime. I think it also validates that the table has the same number of columns.
This of course isn't an issue until some one adds a column in Dev that hasn't been deployed to Live. You build your package on dev, try and deploy and it fails initially because the columns don't exist in test (even if they are not being used).
So the answer is, use a SQL query and only specify the columns you want. That way you can guarentee that if those columns exist in your table(s) you will be fine, there will be no problems if anyone changes that table.
Personally I think this is a bug, but I think its best practice any way.
(ps sorry Jamie if you've already posted this :)