The OLE DB Destination supports using a variable to get the name of the target table. This allows you to switch the destination table dynamically, but there is a caveat. The columns in each table that you will use must match, or the OLE DB Destination will give a NEEDS_NEW_METADATA error.
What’s a potential way that you might use this capability? Well, one recent scenario posed on a forum was this: A set of rows should be loaded to Table A, unless Table A already had rows in it. If it did, the rows should be loaded to Table B. This is fairly straightforward to implement in SSIS, thanks to expressions and the OLE DB Destination working with a variable.
To set this up, I started with an Execute SQL Task. It was configure to retrieve the record count for Table A into a variable called User::RecordCount. A second variable, User::TableName, was created to hold the table name. The value for this variable is determined from the following expression:
This expression evaluates the RecordCount variable. If it is equal to 0, Table A is used, otherwise Table B is used.
The Execute SQL task is connected to a data flow, with an OLE DB Source and the OLE DB Destination. The OLE DB Destination is configured to use the variable for the table name:
That’s all there is to it. I’ve posted a sample package to my SkyDrive here:
A few notes about the sample: There are three tables involved, the source (Numbers) and two destinations (TableA and TableB). All three tables have a single Integer column (named ID in Numbers, and ColumnA in TableA and TableB). The scripts for these are not included in the sample.