CODEWITHSUNDEEP
sql-serverssisextract
El Ronnoco
El Ronnoco

Reputation: 11922

Cross server SSIS automated extract with dynamic tables

You'll have to forgive my ignorance on this - I'm generally a one server kind of guy!

I have a live and reporting server we'll call LIVE and REP.

I have a set of tables on LIVE, some data from which needs to be PIVOTted every night onto the REP server. This already happens through a SSIS package - trouble is - *the extract script is fixed but I need it to be dynamic.

ie. I need to have a new table on LIVE which defines what exactly is extracted to REP.

This means that I want to

I'm concerned because I know SSIS can be funny about table definitions etc etc.

I'm not asking about how to build the dynamic SQL - I've already done that part, and it runs fine entirely on LIVE - but it needs to be cross-server.

Has anyone ever done this before? What is the best practice? Am I even allowed to EXEC dynamic SQL within a SSIS package, and can this be scheduled?

Upvotes: 0

Views: 784

Answers (1)

Petar Vučetin
Petar Vučetin

Reputation: 3615

This might give you some ideas:

Create main workflow where you construct drop tables dynamically and create list of tables by creating a result set:

Main Workflow

Setup a loop that iterates through each table in result set:

For each loop setup 1

For each loop storing current table in variable

Then in your data flow task you can read from one connection / table and write to another:

Data flow task

Upvotes: 2

Related Questions