Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

How To: Use the Same Data Flow Column Twice As a Parameter in SSIS

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.

Comments

Posted by mudzana on 23 May 2011

good article

Posted by Andy Warren on 23 May 2011

Isnt it an option to declare a local variable and set it equal to the question mark, and then go from there?

Posted by tmcdermid on 29 May 2011

Not too sure what you mean, Andy - by "local variable" do you mean an SSIS variable?  Or a variable in the T-SQL?  And what do you mean by "setting it equal to a question mark"?

Posted by Gareth Jones on 20 March 2014

I was wondering if you could do something like this to avoid having multiple copies of your variable

DECLARE @theDate DATETIME

SET @theDate = ?

UPDATE Whatever

SET Something = @theDate

WHERE

   anotherThing < @theDate

Leave a Comment

Please register or log in to leave a comment.