asked
Union All with sources that occasionally do not exist
Hi, I am using an SSIS package to draw data from a DB2 database via an ODBC connection. There are 8 source files/tables that have identical columns and datatypes. Using a 'Union All' transformation I am able to join all the data together into one table. The problem I have is that occasionally one or more of the source tables may no longer exist, and at a later stage may then exist again. I essentially need the package to still succeed with the remaining tables without needing my intervention to remove the datareader source for the non-existent table. One solution I had in mind is to use script components to check if each table exists before attempting to draw data from it. I have done this previously but only with local tables on SQL Server and not DB2 tables on an external server. Any ideas on how to approach the problem will be greatly appreciated.
My logic would say to have 8 connections to the sources and handle the availability (or otherwise) of each one and then pass the data into a shared location (loading table) and then have a single logic process manipulate the data from there.
My solution is as follows: I have realised that I am able to access the DB2 source tables via a linked server. There is never more than a handful of records in each of these tables so the performance of the linked server is not much of an issue. Before I attempt to extract data from each source I include a script task that checks whether the table exists. If it exists the 'success' constraint carries the flow on to extract the data. If the source table does not exist, then the 'failure' constraint bypasses the extraction of data from that source.
This sounds very similar to the solution FatherJack provided unless I am missing something. Either way, if you consider the matter closed please select an accepted answer. I think FatherJack's answer is quite good, but you can select your own if you prefer.
