Every once in a while, you'll have a slightly more complex UPDATE statement in an OLE DB Command or Destination. You'll use an UPDATE statement or a destination table that needsto use data from one column in your data flow several times. A typical example is a range update:UPDATE table1
SET column1 = ?
WHERE datecolumn1 <= ?
AND datecolumn1 > ?
Yes, best practices say that you should probably avoid using the OLE DB Command entirely, but if you aren't updating many records and you don't mind the poor interface, it does get the job done.
What You Can't Do
The OLE DB Command interface asks for the statement (like above) that has question marks as placeholders for the values you're going to pass in. The OLE DB and ADO.Net Destinations allow you to identify tables, or craft a SELECT statement to identify a table. They also have a interface for associating those numbered parameters or destination columns to the columns you have available in the data flow. One of the many faults of this interface is that it doesn't permit two parameters/destination columns to be associated with one column in the data flow - you just can't do it.
 |
| Another Splice Won't Hurt... |
?What You Can Do ??So if the interface only allows 1:1 associations of parameters in your statement to columns in the data flow... then you just need to duplicate a column in the data flow. Add a Copy Column transform, or a Derived Column transform to the design surface just prior to the OLE DB Command or Destination transform to create a second column that contains the same data. Now you've got what you need to configure your OLE DB Command or Destination properly.
