Union All with sources that occasionally do not exist


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.

more ▼

asked Aug 20, 2010 at 01:57 AM in Default

avatar image

1 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Aug 20, 2010 at 02:38 AM

avatar image

Fatherjack ♦♦
43.8k 79 101 118

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Aug 20, 2010 at 04:34 AM

avatar image

1 1 1 1

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.

Aug 20, 2010 at 09:18 AM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Aug 20, 2010 at 01:57 AM

Seen: 1706 times

Last Updated: Aug 20, 2010 at 02:31 AM

Copyright 2018 Redgate Software. Privacy Policy