dexter
dexter

Reputation: 7213

Transferring SQL Data with SSIS in VS2012

I am trying to do this in SSIS (but it does not seem trivial) copy data from source to destination in this manner:

     copy->(select from source.TableA where source.CallID > (select max(destination.TableA.CallID) from destination.TableA) -> to destination

So I am trying to copy some delta data from source based on the aggregate Max(CallID) from destination.

Upvotes: 0

Views: 138

Answers (1)

Bill
Bill

Reputation: 4585

In SSIS this is typically done as two steps. First, create a variable scoped at the package level. Use an Execute SQL task to run your max(destination.TableA.CallID) query and stuff that value into the variable.

Then, in your data flow, use a parameter query and pass the variable in for the parameter. select from source.TableA where source.CallID > ? (OLE DB Source)

There are lots of examples online for this.

EDIT:
Here are instructions for setting a variable:
http://dataqueen.unlimitedviz.com/2012/08/how-to-set-and-use-variables-in-ssis-execute-sql-task/

and instructions for using it:
http://bisherryli.wordpress.com/2011/03/06/ssis-pass-a-variable-to-a-ole-db-source-in-a-data-flow/

Upvotes: 1

Related Questions