INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Dynamic Dataflow destination

Dynamic Dataflow destination

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close