Dynamic Dataflow destination

[TomCarnahan]

Hi,

Question: in SSIS, what is the best way to create a dynamic Data Flow destination based on package-level variables?

I need to extract data from one SQL Server (2014) instance, database, and table and import it into another SQL Server instance and database where the instance name and the database name change but the table name remains the same.

Here is what I have tried:

Package level variables:

- "TargetInstance" (value: Instance2)
- "TargetDatabase" (value: Database2)
- "TargetInstanceDatabase" (derived: Instance2.Database2) that is the concatenation of the first two variables.
- "StartDate"
- "EndDate"

I have a connection for the target database that has its Initial Catalog and Connection string as expressions based on the variables (above).

In the Data Flow Task, I have a Source (OLE DB) that uses a query with two parameters, StartDate and EndDate. This produces the desired data set.

For the OLE DB destination, I initially set this Destination to a real database and select the table to receive the data. But when I switch to new derived connection based on the variables, I don't get a table and it tells me the database is offline. I checked both expressions and they correctly evaluate to the real database name and connection string.

Is there something I am doing wrong? Is there a better way to change the Data Flow destination dynamically?

Any help would be appreciated.

Thanks

--- Tom