4

I am trying to write an SSIS package to transfer data from one database to another (straight copy, the tables that I am transferring to and from have the same structure). I am selecting a subset of the records (the ones that have been created or modified since the last time the package was ran) and I am trying to dump them to a sproc on the destination database which will determine which records need to be updated and which records need to be inserted.

How do I do I either do this inside a Data Flow object, or transfer the records out of the object so I can do it with an Execute SQL Task?

I don't want to use an OLE DB Command since that only works on one record at a time. The two databases are on different machines at different locations, and I'd like this package to take as little time as possible to run since I'm writing it to replace a DTS package that takes way too long to run (it deletes the entire contents of the destination table and re-copies over everything, changed or otherwise)

Hypersapien
  • 617
  • 2
  • 8
  • 23
  • Have you considered using SQL Replication to update the remote DB from the source one? Should be easier to setup if your source tables have (or can easily be updated to have) primary keys defined and pretty reliable. – JohnLBevan Oct 19 '12 at 21:55

1 Answers1

2

SSIS does not support table valued parameters. There are several work arounds for what you are trying to achieve:

  • There are several 3rd party UPSERT/MERGE destinations, including this one on CodePlex http://ssisctc.codeplex.com/wikipage?title=MERGE%20Destination
  • You could use an OLEDB destination to insert the rows into a temp table on the server, then run your stored procedure against the temp table from an Execute SQL task. (This is what we have done on my current project)
  • You could write a custom .NET script destination
  • You could use a Lookup Transformation to see if they exist in the table, then insert the rows that don't exist and run OLEDB Command against the others which would at least be better than running it against every row
Pete Carter
  • 2,691
  • 3
  • 23
  • 34
  • Use the Lookup Transformation but route the changed rows to a staging table and perform an update in an execute SQL task after the data flow. This pattern allows you to avoid the RBAR pain of the OLEDB Command. – billinkc Oct 21 '12 at 14:47